VBA Autofilter --> what if criteria doesn't exist? Error handling??? Please help...

  • Thread starter Thread starter bertbarndoor
  • Start date Start date
B

bertbarndoor

Hi, I'm asking excel to update an autofilter with the following line:

Selection.AutoFilter Field:=61, Criteria1:="2"

However, I just noticed that 2 is not always in the list. In that
case, it doesn't auto filter anything and all rows are still visible.
Is there a way that I can get it to display zero rows if it can't
autofilter by the desired criteria?

Thanks, Rob (bertbarndoor)
 
Hi,

I checked your code and it does not show any rows if I use it on my PC.

I think there must be some other lines in your code that are affecting the
view. For me the below line of code worked fine.
 
Sub PrintFormat()
'
'
' VB Created 10/17/2007 by Robert Brando
'

' Updates Autofilter

Selection.AutoFilter Field:=61, Criteria1:="2"

' Widens rows as necessary

Rows("4:1000").EntireRow.AutoFit
Application.CutCopyMode = False

' Sets Print Area

Dim lastCell As Range
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
Do Until Application.Count(lastCell.EntireRow) <> 0
Set lastCell = lastCell.Offset(-1, 0)
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1),
lastCell).Address


'
End Sub
 
Here is my full code??? Is it one of the other lines that could be
screwing it up?

Sub PrintFormat()
'
'
' VB Created 10/17/2007
'

' Updates Autofilter

Selection.AutoFilter Field:=61, Criteria1:="2"

' Widens rows as necessary

Rows("4:1000").EntireRow.AutoFit
Application.CutCopyMode = False

' Sets Print Area

Dim lastCell As Range
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
Do Until Application.Count(lastCell.EntireRow) <> 0
Set lastCell = lastCell.Offset(-1, 0)
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1),
lastCell).Address


'
End Sub
 
Back
Top