Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Find out if there are any results after filtering

0

In vba I'm trying to find out if there is any results after a filter is applied to a data set to proceed with futher calculation.

So I would want to know excluding headers on which the filters are applied, there is a resultant data set or blank

Is there a way to do that in vba?

Appreciate your help

Answer
Discuss

Discussion

Did you try my answer/file, Dr. Liss? 
John_Ru (rep: 6092) Nov 1, '22 at 10:19 am
I'll take the absence of any reply as a "No" then! 
John_Ru (rep: 6092) Nov 23, '22 at 8:25 am
Add to Discussion

Answers

0

Dr Liss

In the attached file, there's table of data, a yellow cell J1 for a filter value and a blue button to run the macro below. Change the value of J1 to 20 say and it will tell you there are no results (since column D has no values >=20).

The key thing is the test in bold below- if there's no results from the filter, the row of the last cell will be the same as the header row (1) and trigger the Else condition:

Sub CheckFilter()
Dim OutRng As Range

Set OutRng = Range("A1:F10")

' filter column D for J1
OutRng.AutoFilter Field:=4, Criteria1:=">=" & Range("J1").Value, VisibleDropDown:=Fals
' see last filtered cell is after header row (1)
If Cells.SpecialCells(xlCellTypeLastCell).Row > 1 Then
    MsgBox "Displaying results"
    Else
    MsgBox "No results"
End If
'clear filter
OutRng.AutoFilter

End Sub

Hope you can modify this to work for you.

Discuss

Discussion

Did that work? 
John_Ru (rep: 6092) Nov 2, '22 at 7:51 pm
Have you forgotten to reply/ select, Dr Liss?
John_Ru (rep: 6092) Nov 3, '22 at 3:22 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login