vbaadvanced-filter

Update advanced filter for a range which has new rows added

I have a worksheet containing a list of criteria in cells A1 to J2. Row 1 is the header.

Below i have a a table with all my data items. Cells A8 to J with a dynamic numbre of rows

Sheets("D0022").Range("A8:" & Sheets("D0022").Cells(Rows.Count, "J").End(xlUp).Row).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("D0022").Range("a1:j2"), Unique:=True

When i run this i get an Runtime 1004 Application defined or object defined error

Can i have assistance on why my VBA code does not work

Solution

  • Try changing of Sheets("D0022").Range("A8:" & Sheets("D0022").Cells(Rows.Count, "J").End(...

    with

    Sheets("D0022").Range("A8:J" & Sheets("D0022").Cells(Rows.Count, "J").End(...

    "A8:J" instead of "A8:".

    Otherwise you do not set the column of the range.

    Sheets("D0022").Cells(Rows.Count, "J").End(xlUp).Row calculates only the last row of column J:J...