Solved: Show MsgBox if autofilter results in zero

[U_Shrestha]

Hi all,

I have the following code that I used to filter data in a table. Sometimes the result of the autofilter applied is zero/no data/row. Is such situation, is it possible to display a MsgBox saying "You have no issues for this period!"? Here is the code:
Sub ActiveGreaterThanHundredTwenty()
' ActiveZeroToThirty Macro
' Macro recorded 4/16/2008 by ushrestha'
Application.ScreenUpdating = False
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
Selection.AutoFilter field:=18, Criteria1:="Active>120"
Application.ScreenUpdating = True
End Sub

[figment]

in what cell dose the table start?

[U_Shrestha]

The table starts from A2. Column 2 has the table headline and data starts from 3rd row (A3).

The table has 18 columns (A:R), the rows keep increasing.

[figment]

i am makeing the assumption that there is nothing below the table on the sheet, so if thats true give this a try.

Function Testing_Table() As Boolean ' will return true if any column is empty
Dim a As Long
Testing_Table = False
For a = 1 To 18
If Cells(2, a).End(xlDown).Row = Rows.Count Then Testing_Table = True
Next
End Function

Sub ActiveGreaterThanHundredTwenty()
' ActiveZeroToThirty Macro
' Macro recorded 4/16/2008 by ushrestha'
Application.ScreenUpdating = False
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
Selection.AutoFilter field:=18, Criteria1:="Active>120"
' pick the function you want to use for desired affect
If Testing_Table() Then MsgBox "you have no issues for this period!", vbOKOnly, "YAY No Issues"
Application.ScreenUpdating = True
End Sub


Function Testing_Table2() As Boolean ' will return true only if all columns are empty
Dim a As Long
Testing_Table2 = True
For a = 1 To 18
If Cells(2, a).End(xlDown).Row <> Rows.Count Then Testing_Table2= False
Next
End Function

[tstav]

A different solution to this issue:
Let me first put it in words, before we get to the code.

When a filter is applied to a range, the resulting visible rows may or may not be consecutive. If we apply the SpecialCells(xlCellTypeVisible) to the filtered Area, we get a Union of all the ranges (consecutive or not) that constitute this Area.

Now, the Address of this Union is a comma delimited string of addresses (e.g."$A$1:$R$3,$A$5:$R$9") or a solid range address.

To ask whether the filter produced a zero result, is the same as asking whether the Visible Area constitutes of only one Range that has only one row (i.e. the titles row).

So, we will do the following:
We will Split the Address of the filtered Area (by comma) and check whether the Upper Bound of the Split is 0 (i.e. there is only one range in this Area).
We will also check whether the rows.count of this range is one (i.e. the titles row).
If both conditions are true, then we have no visible rows of data, hence the filter shows only the titles.

The code is very short and very straightforward.
Sub ActiveGreaterThanHundredTwenty()
Dim rng As Range
' Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Set rng = ActiveSheet.UsedRange
rng.AutoFilter Field:=8, Criteria1:="Active>120"
Set rng = rng.SpecialCells(xlCellTypeVisible)
If UBound(Split(rng.Address, ",")) = 0 And Range(Split(rng.Address, ",")(0)).Rows.count = 1 Then
MsgBox "No data"
End If
' Application.ScreenUpdating = True
End Sub


Edit: Slight rephrasing of the comments.

[U_Shrestha]

Sorry, about the late response. I have a question about tstav's code. What does the following code mean?
ActiveSheet.AutoFilterMode = False
Set rng = ActiveSheet.UsedRange
Well, in my actual table sheet, there is combobox from which I choose different macros that will filter the table. The macro that I posted "Active>120" is just one of them. What I wanted was,when I choose the "Active>120" macro, first the table shows all data and then filter column that has the value "Active>120" value. Right now, this works only if I choose other macros that filter the data in a similar fashion but different criteria (e.g "ActiveIssues0-30day"), but if I choose "Show All Data" macro to unfilter all columns then the macro "Active>120" doesn't work. How can this be corrected? Thanks.

[figment]

if you post the worksheet, it would help us to debug this problem. if the data on the sheet is confidential, then just fill the talble with gargabe before posting.
From what you are saying, its hard to tell what could be wrong. are you always auto filtering the same columns? or ar those filters applyed to other columns first? if you could post the other code as well it would help.

[tstav]

Hi U_Shrestha,

this is a version of my post#5 code, that goes along with what you seem to be interested in: i.e. range selections.
If you want to have the data range selected before applying the filter, that's fine.
I have also reverted the code to the lines you used for showing all data.
My version did exactly the same (showed all data), plus it made the filter arrows go away. Same thing.

If you have extra data on your sheets (apart from the data to be filtered) then don't use my previous code. You can try the one below.

As for the rest of your comments, it's not really clear to me what you mean.

Regards,
tstav
Sub ActiveGreaterThanHundredTwenty1()
'Find whether a filter showed no data
Dim rng As Range
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
' ActiveSheet.AutoFilterMode = False
Selection.AutoFilter Field:=1, Criteria1:="Active>120"
Set rng = Selection.SpecialCells(xlCellTypeVisible)
If UBound(Split(rng.Address, ",")) = 0 And Range(Split(rng.Address, ",")(0)).Rows.count = 1 Then
MsgBox "No data"
End If
End Sub

[U_Shrestha]

Actually, my actual sheet looks like this:

http://www.vbaexpress.com/forum/showthread.php?p=140813#post140813

I had previously posted it as solved but I realized I need to have a some modification and also include a message box. I posted both the issues together in the attachment because I need to have both the code work together. Can you please take a look? Thanks.

[figment]

i think this fixes most the issues
8521

[tstav]

Plus, if you want the combobox1 filled with values right from the start (without having to switch to Sheet1 and back) you can remove the Worksheet_Activate event and place the following in the Workbook_Open event.
Private Sub Workbook_Open()
With Worksheets("AuditIssues").ComboBox1
.Clear
.AddItem "Active Issues 0 to 30 days old"
.AddItem "Active Issues 31 to 60 days old"
.AddItem "Active Issues 61 to 90 days old"
.AddItem "Active Issues 91 to 120 days old"
.AddItem "Active Issues >120 days old"
.AddItem "All Active Issues"
End With
End Sub

[U_Shrestha]

thanks figment, i will look at the attachment.

tstav, after putting your code in Workbook_open(), do I need to put that code anywhere else or not? Currently I have following code in Workbook_Open(), how can I merge both together?
Private Sub Workbook_Open()
Dim ws As Worksheet

For Each ws In Worksheets
ws.Protect "password", AllowFiltering:=True, userinterfaceonly:=True, _
Contents:=True, DrawingObjects:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True


Next ws

End Sub

[figment]

just copy from the with to the end with of tstav's code to the end of the sub

[U_Shrestha]

hello figment,

thank you very much for correcting the code. can you please tell/teach me how you changed 5-macros into 1-macro. previously i had assigned one macro each for each combobox selection? i didn't understand this :)
Sub filteractive(activerange As String)
'
' ActiveZeroToThirty Macro
' Macro recorded 4/16/2008 by ushrestha'
Application.ScreenUpdating = False
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
Selection.AutoFilter Field:=16, Criteria1:="Active" & activerange
Application.ScreenUpdating = True
End Sub

[figment]

When i see a set of mcros like your origenal 5 macros, i look to see what is diffrent between them. in the case of your origenal 5 it was the line "Criteria1:="Active" & activerange" in this case the Word Active was always the same, but the characters that came ofter it changed. Being that this was a small change i was able to replace the characters that folowed the word Active, with a variable. in this case activerange. now all i had to do was call the function with a diffrent value of activerange, to represent the diffrences from in the previouse functions.

i hope this explination helps

[tstav]

just copy from the with to the end with of tstav's code to the end of the sub
u,
I can see you are protecting your sheets.

Just to be on the safe side, better put my code in the beginning of the Workbook_Open event (before the For each... loop), before setting the protections.

[U_Shrestha]

If Me.ComboBox1.Value = "Active Issues 0 to 30 days old" Then
Call filteractive("0-30")

So, now it is looking at column "O" or "P" for active range? (Column O had range of days "0-30, 31 to 60 etc), and column P had the range as "Active0-31,Active31-60" etc?

[figment]

it still only looks at P but it combines the Input to the function , in this case "0-30" with the work "Active" to generate the correct filter, much like you Column P combines to columns to generate its value.

[U_Shrestha]

It's really amazing how you put everything together, thank you very much for your help and explanation. It has been a great help for my project.

[U_Shrestha]

Hello tstav,

I used your code as follows, what modification do I need? )I have another macro to unprotect all worksheets at once with a password, does it make any difference?:

Private Sub Workbook_Open()
Dim ws As Worksheet

For Each ws In Worksheets

ws.Protect "password", AllowFiltering:=True, userinterfaceonly:=True, _
Contents:=True, DrawingObjects:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

Next ws
With Worksheets("AuditIssues").ComboBox1
.Clear
.AddItem "Active Issues 0 to 30 days old"
.AddItem "Active Issues 31 to 60 days old"
.AddItem "Active Issues 61 to 90 days old"
.AddItem "Active Issues 91 to 120 days old"
.AddItem "Active Issues >120 days old"
.AddItem "All Active Issues"
End With
End Sub

[tstav]

If protecting the sheets doesn't interfere with filling the combobox1 with values, then you need change nothing.
If it does, then move the "With Worksheets...End With" code before the "For Each ws...Next ws".

The code that unprotects sheets would not interfere with anything. Put it anywhere it suits you.

[U_Shrestha]

hello tstav,

Thank you very much for your help and the response. Your help is very much appreciated. :)

[U_Shrestha]

hello again,

I am having problem with getting an actual count on Unique numbers (Station Numbers from column 1). I have attached the sheet which exactly looks like my real file. Could you please help me by taking a look at it. Everything else is perfect,but it is showing wrong count of station number. Example, when I select 0-30 days old, it shows, 36 issues with 43 stations, and when i select >120 days, then it still shows 43 station.

[tstav]

Add the line with the arrow you'll see below in this code:
Private Sub CountStation()
Dim Checkrg As Range, CheckCell As Range, Filterrg As Range
Dim Counter As Long, RowEnd As Long

With ActiveSheet

With .AutoFilter.Range

On Error Resume Next
Set Filterrg = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)

If Filterrg Is Nothing Then
TextBox1.Text = Counter '<----------- Add this line here
Exit Sub
Else

RowEnd = .Offset(1, 0).SpecialCells(xlCellTypeVisible).End(xlDown).Row
Set Checkrg = .Range(.Cells(2, 1), .Cells(RowEnd, 1))

For Each CheckCell In Checkrg
If CheckCell.Value <> CheckCell.Offset(1, 0).Value Then
Counter = Counter + 1
End If
Next
End If
End With

TextBox1.Text = Counter
End With

Set Checkrg = Nothing
End Sub

[U_Shrestha]

Now it is perfect!! I tried in my actual file and it works. Thank you sooooo much. :clap:

[U_Shrestha]

Sorry, the problem came back again even after making changes. It had worked in the beginning.

[tstav]

Sorry I can't look into it right now u, gotta go. I'll be back tomorrow.
tstav

[U_Shrestha]

sure, I will wait. have a good night.

[figment]

i know theres a better way to do this, if i think of it i will post it later but for now replace your sub countstation function with the folowing two functions and it should work

Private Sub CountStation()
Dim Checkrg As Range, checkcell As Range, Counter As Long
ReDim checklist(0 To 0) As String
Counter = 0
With ActiveSheet
Set Checkrg = .Columns(1).SpecialCells(xlCellTypeVisible) '<-set to appropriat column
For Each checkcell In Checkrg
Counter = Counter + check(checklist, checkcell.Value)
Next checkcell
TextBox1.Text = Counter - 1
End With
Set Checkrg = Nothing
End Sub

Private Function check(ByRef checklist() As String, checkcell As String) As Long
Dim a As Long
check = 1
For a = LBound(checklist) To UBound(checklist)
If checklist(a) = checkcell Then
check = 0
Exit Function
End If
Next
ReDim Preserve checklist(LBound(checklist) To UBound(checklist) + 1)
checklist(UBound(checklist)) = checkcell
End Function

[tstav]

Hi u,

I went over all the code in your file (which I hadn't done before, I was only looking into things you asked help about).

The code in Private Sub CountStation() seems to be giving entirely wrong results. So, I did a manual cross-check, to make sure of the irrelevance of the result.
What I found was that although there were only 3 unique stations, the textbox result showed 39 (the numbers are fictitious).
Have you ever done any manual cross-check yourself? Please do.

I revised the code. Here it is.

PS. I haven't read figment's code yet. Maybe he's already gotten the mistakes.

Private Sub CountStation()
Dim CheckCell As Range, Filterrg As Range
Dim colStation As Collection
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set Filterrg = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
If Err Then ' No visible data rows
Err.Clear
TextBox1.Text = 0
Else
Set colStation = New Collection
'Store unique items in collection (store item plus key)
'Collections accept only unique keys (second argument).
'Keys already added get rejected
For Each CheckCell In Filterrg
colStation.Add CStr(CheckCell.Value), CStr(CheckCell.Value)
Next
'This is the count of unique items
TextBox1.Text = colStation.Count
Set colStation = Nothing
End If
End With
End Sub

[tstav]

Figment's code (post#29) will also give the correct result.

[U_Shrestha]

Many many thanks to figment and tstav for your code and spending your time. Both the codes work :) !!!

I used it in my actual workbook and I have it running now. Thanks so much.

[U_Shrestha]

Is there a particular reason why this code results in Error 1009 when the command button is triggered while the table is already showing all the data in the table?
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
With Me
If .AutoFilterMode = True Then
ActiveSheet.ShowAllData
TextBox1.Text = ""
TextBox2.Text = ""
End If
End With
Application.ScreenUpdating = True
End Sub

[tstav]

Are you sure it is error 1009 and not 1004 (Method ShowAllData of class Worksheet failed)?

For ShowAllData method to work, some lines must already be hidden. To check that, check if FilterMode property is True.

By checking the AutoFilterMode property, you are actually checking only whether the filter arrows are visible or not. The arrows are of course visible but there is nothing hidden (all lines are visible) and so ShowAllData fails.

So change the next line to
'If .AutoFilterMode = True Then
If .FilterMode = True Then

[U_Shrestha]

I changed the line and now there is no error message. Thanks again tstav.

I wonder what the difference is between the two lines. If I need to ShowAllData from any given table, is it always safe to use
If .FilterMode = True Then

rather than,
If .AutoFilterMode = True Then

[tstav]

Yes, do that.

[U_Shrestha]

Thanks.