CODEWITHSUNDEEP
vbaadvanced-filter
Ingram
Ingram

Reputation: 674

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

Upvotes: 1

Views: 76

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

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...

Upvotes: 2

Related Questions