I'm stumped on how to create a dynamic range using advanced filter and VBA.
This static code works:
Sheets("CMBS Inventory").Range("A2:AD680").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Sheets("Filters").Range("A1:C3"), CopyToRange:=Range("A1"), Unique:=False
However, when I try to change it to using a dynamic range, I have an error that I can't resolve (Run-time error '1004'). LastCellRow and FinalCol have the expected values, but am stumped as to why I can't define the range correctly.
Sheets("CMBS Inventory").Select
LastCellRow = Cells(65536, 2).End(xlUp).Row
FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column
Sheets("Conduit").Select
Sheets("CMBS Inventory").Range(Cells(2, 1), Cells(LastCellRow, FinalCol)).AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Sheets("Filters").Range("A1:C3"), CopyToRange:=Range("A1"), Unique:=False
What am I missing?
Thank you!
This static code works:
Sheets("CMBS Inventory").Range("A2:AD680").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Sheets("Filters").Range("A1:C3"), CopyToRange:=Range("A1"), Unique:=False
However, when I try to change it to using a dynamic range, I have an error that I can't resolve (Run-time error '1004'). LastCellRow and FinalCol have the expected values, but am stumped as to why I can't define the range correctly.
Sheets("CMBS Inventory").Select
LastCellRow = Cells(65536, 2).End(xlUp).Row
FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column
Sheets("Conduit").Select
Sheets("CMBS Inventory").Range(Cells(2, 1), Cells(LastCellRow, FinalCol)).AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Sheets("Filters").Range("A1:C3"), CopyToRange:=Range("A1"), Unique:=False
What am I missing?
Thank you!