Advanced Filter and VBA

jswhip

Board Regular
Joined
Jan 19, 2005
Messages
62
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
The problem is with this expression:

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

Code:
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

Board Regular
Joined
Jan 19, 2005
Messages
62
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Yes, here's an example.

Code:
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
 

Forum statistics

Threads
1,086,122
Messages
5,387,975
Members
402,092
Latest member
S_S

Some videos you may like

This Week's Hot Topics

Top