Advanced Filter and VBA

[jswhip]

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!

 

[Jerry Sullivan]

The problem is with this expression:

Sheets("CMBS Inventory").Range(Cells(2, 1), Cells(LastCellRow, FinalCol)).

Since there is no "." in front of the Cells keywords, those are interpreted as references to the ActiveSheet (which is "Conduit").

Typically it's better not to Select sheets, but rather use fully qualified references so that it doesn't matter what sheet is active.

Here's one way to do that....

Sub AdvFilter()
 Dim LastCellRow As Long, FinalCol As Long
 Dim rData As Range
 
 With Sheets("CMBS Inventory")
   LastCellRow = .Cells(.Rows.Count, 2).End(xlUp).Row
   FinalCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
   Set rData = Range(.Cells(2, 1), .Cells(LastCellRow, FinalCol))
 End With

 rData.AdvancedFilter Action:=xlFilterCopy, _
   CriteriaRange:=Sheets("Filters").Range("A1:C3"), _
   CopyToRange:=Sheets("Conduit").Range("A1"), Unique:=False
End Sub

 

[jswhip]

That worked great - thanks!

For my education, I tried to do something a little different to better understand when a period is needed, etc. Do you know how I could specify the data to filter on without using a named range? In other words, replace "rData" with "Range(Cells(2, 1), Cells(LastCellRow, FinalCol))" in the advanced filter syntax?

Dim LastCellRow As Long, FinalCol As Long
Dim rData As Range

Sheets("CMBS Inventory").Select
LastCellRow = Cells(65536, 2).End(xlUp).Row
FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column
Set rData = Range(Cells(2, 1), Cells(LastCellRow, FinalCol))

Sheets("Conduit").Select

rData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Filters").Range("A1:C3"), _
CopyToRange:=Sheets("Conduit").Range("A1"), Unique:=False

 

[Jerry Sullivan]

Yes, here's an example.

Sub AdvFilter2()
 Dim LastCellRow As Long, FinalCol As Long
 
 With Sheets("CMBS Inventory")
   LastCellRow = .Cells(.Rows.Count, 2).End(xlUp).Row
   FinalCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

   .Range(.Cells(2, 1), .Cells(LastCellRow, FinalCol)).AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=Sheets("Filters").Range("A1:C3"), _
      CopyToRange:=Sheets("Conduit").Range("A1"), Unique:=False
 End With

End Sub