How to skip VBA Code if table filter returns nothing
Category: [Excel] Demo Available
How to skip VBA Code if table filter returns nothing
Requirement:
The user has a macro filtering a table, and the user would like to skip over a chunk of the code if no cells are returned from the filter.
Code:
Sheets("Item Ledger Entries").Select
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=9, Criteria1:= _
"=COR2", Operator:=xlOr, Criteria2:="=COR2 QA"
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=43, Criteria1:= _
"=RAW MAT"
'I'd like to skip everything below this down to the "Sheets("Item..." " on the very bottom if nothing is returned from filtering the range.
Worksheets("Item Ledger Entries").Activate
Set cell = Selection.Find(What:="Entry No.", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=True)
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Worksheets("FG Summary").Activate
Range("A6").Select
ActiveSheet.Paste
'COR 1 Macro
Sheets("Item Ledger Entries").Select
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=9, Criteria1:= _
"=COR1"
Worksheets("Item Ledger Entries").Activate
Set cell = Selection.Find(What:="Entry No.", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=True)
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Worksheets("FG Summary").Activate
Range("A204").Select
ActiveSheet.Paste
Sheets("Item Ledger Entries").Select
Solution:
Basically, it will be a count of filtered areas in the table
Code:
if ActiveSheet.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible).Areas.Count > 1 then
msgbox "There is stuff"
else
msgbox "There is no stuff"
end if
Obtained from the OzGrid Help Forum.
Solution provided by pike.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to select if cell contains any text return text in another cell |
| How to find/return first nonblank value in adjacent cell from column with duplicate values |
| How to use Excel VBA return values with same unique ID numbers |
| How to create VBA return that will return customised results when comparing two worksheets |
| How to look for value in three different ranges and return one of three results |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.