Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter -- what if criteria doesn't exist? Error handling??? Please help...
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter -- what if criteria doesn't exist? Error handling?
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. -- Pranav Vaidya VBA Developer PN, MH-India "bertbarndoor" wrote: 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter -- what if criteria doesn't exist? Error handling?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter -- what if criteria doesn't exist? Error handling?
Nevermind, it was the row-autofit that was doing it. Thanks.
-Rob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter -- what if criteria doesn't exist? Error handling?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent getting all rows if Autofilter criteria doesn't exist | Excel Programming | |||
Selection.AutoFilter Field / Criteria = criteria sometimes non-existing on worksheet | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
3 criteria must exist in adjoining cells then rtn val from 4th | Excel Discussion (Misc queries) | |||
Error handling with a handling routine | Excel Programming |