[SOLVED] Select a number of columns in an excel table for advanced filter

Select a number of columns in an excel table for advanced filter

I have created raw data within a table, I use an advanced filter by VBA but when I run the codes, the table disappeared so I try to write a code to select many columns but I received an error every time I tried

The code

'Assuming the name of the table is "RawData"
LastTransRow = Sheet9.ListObjects("RawData").DataBodyRange(19999, 1).End(xlUp).Row 'Last Transaction Row
Sheet9.ListObjects("RawData").DataBodyRange("G1:A" & LastTransRow).AdvancedFilter xlFilterCopy, CriteriaRange:=Sheet11.Range("A2:B3"), CopyToRange:=Sheet11.Range("K2:E19999"), Unique:=True

Solution

I added references to the Workbook that contains the code (ThisWorkbook) in order to ensure that operations are performed on its child sheets. Reset some arguments (eg: "G1:A" >>> "A1:G", "K2:E19999" >>> "E2:K19999"). Worked on my machine. Give it a try.

Sub AdvFilterTest()
    Dim LastTransRow As Long

    'Assuming the name of the table is "RawData"
    With ThisWorkbook.Sheets("Sheet9")
        .Activate
        LastTransRow = .ListObjects("RawData").DataBodyRange(19999, 1).End(xlUp).Row 'Last Transaction Row
        .ListObjects("RawData").DataBodyRange.Range("A1:G" & LastTransRow).AdvancedFilter xlFilterCopy, CriteriaRange:=ThisWorkbook.Sheets("Sheet11").Range("A2:B3"), CopyToRange:=ThisWorkbook.Sheets("Sheet11").Range("E2:K19999"), Unique:=True

    End With
End Sub