Htykuut

I've searched a lot and tried a lot of different things, but I can't get the following to work.

I have a range of IDs that I generate. I then want to filter a range of data to produce a filtered output of just the specified IDs. However, whatever I try, my filtered output just returns a blank range.

Please note: my range of IDs varies in size each time I run this process. I've included a very simplistic set of data to illustrate my point - my real data is far more complex than my example.

The code I have at the moment:

Sub Test()



    Dim Template As Workbook

    Set Template = ThisWorkbook

    Dim IDs, Report As Worksheet

    Set IDs = Template.Worksheets("IDs")

    Set Report = Template.Worksheets("Report")



    Dim LastRowIDs As Long

    LastRowIDs = IDs.Range("A" & IDs.Rows.Count).End(xlUp).Row



    Dim IDsArray As Variant



    IDsArray = IDs.Range("A2:A" & LastRowIDs)



    Dim LastRowReport As Long

    LastRowReport = Report.Range("A" & Report.Rows.Count).End(xlUp).Row



    Report.AutoFilterMode = False

    Report.Range("A1:C" & LastRowReport).AutoFilter Field:=1, Criteria1:=Application.Transpose(IDsArray), Operator:=xlFilterValues



End Sub

I would say I'm a moderately basic user of VBA so please explain any answers.

Range to filter on

Data to be filtered

Desired output

Excel likes it's autofilter arrays as strings. So you need to convert the array to string values. Then it works.

Sub Test()



    Dim Template As Workbook

    Set Template = ThisWorkbook

    Dim IDs, Report As Worksheet

    Set IDs = Template.Worksheets("IDs")

    Set Report = Template.Worksheets("Report")



    Dim LastRowIDs As Long

    LastRowIDs = IDs.Range("A" & IDs.Rows.Count).End(xlUp).Row



    Dim IDsArray As Variant

    Dim sTemp As String, i As Integer

    IDsArray = Application.Transpose(IDs.Range("A2:A" & LastRowIDs))

    For i = LBound(IDsArray) To UBound(IDsArray)

        sTemp = "," & IDsArray(i) & sTemp

    Next i

    IDsArray = Split(Mid(sTemp, 2), ",")



    Dim LastRowReport As Long

    LastRowReport = Report.Range("A" & Report.Rows.Count).End(xlUp).Row



    Report.AutoFilterMode = False

    Report.Range("A1:C" & LastRowReport).AutoFilter Field:=1, Criteria1:=IDsArray, Operator:=xlFilterValues



End Sub