(Solved) – Excel VBA apply advanced filter when report’s headers come on different rows

  • by
(solved)-–-excel-vba-apply-advanced-filter-when-report’s-headers-come-on-different-rows

I want to apply Advanced filters to reports, the headers of which can be on row 24, on other reports on row 27, and so on. Is there a way to adapt the code to identify which is the row on which the headers are, independently of the type of report? I would also want to adapt the code to apply the filters to different columns, on some reports the columns I would like to filter are AT and AU, on others they would be BK and BL. Thanks!

My code is the following:

Sub GetUniqueList()

'   Defines variable
    Dim LastRow As Long

    With ActiveWorkbook
        Sheets(1).Activate
    End With

    With ActiveWorkbook.Sheets(1)
        LastRow = .Range("BK" & .Rows.Count).End(xlUp).Row
    End With


    MsgBox ("The last row of data is: " & LastRow)


'   Gets the unique list of recommendations and copies it on column BA
    With ActiveWorkbook.Sheets(1)
        Range("BK27:BL" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("BY27"), Unique:=True
    End With


'   Goes to Unique table
    ActiveWorkbook.Activate
    Range("BY27").Select

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *