ShowAllData

M

mt

Board Regular
Joined
Feb 24, 2006
Messages
134
I want to make sure a worksheet is unfiltered before proceeding with the rest of the routine. I can make sure that there is data to unfilter with verifying the last row is = to > than 5, but if the data is already unfiltered, what is the correct syntax to code this. I try to avoid using the "On Error Resume Next" command.

Thanks.
Mike

Code: 
With Worksheets("ActualData")
    aRow = Range("A" & Rows.Count).End(xlUp)
        If aRow >= 5 And .ShowAllData = False Then
            .ShowAllData
        End If
    End With
[code]
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Is the sheet filtered using AutoFilter?
Code: 
With Worksheets("ActualData")
    If .AutoFilterMode Then
        Worksheets("ActualData").ShowAllData
    End If
End With
 
Upvote 0
Norie,

Thanks. I still get the error "ShowAllData method of worksheet class failed"
Mike

Code: 
With Worksheets("ActualData")
            If .AutoFilterMode Then
            Worksheets("ActualData").ShowAllData
        End If
    End With
 
Upvote 0
Norie,

Thanks. I still get the error "ShowAllData method of worksheet class failed" The data could be filtered by either Autofilter or Advanced Filter.
Mike

Code: 
With Worksheets("ActualData")
            If .AutoFilterMode Then
            Worksheets("ActualData").ShowAllData
        End If
    End With
 
Upvote 0
You will find that Norie's suggestion will work if the sheet is already filtered, but if the filters are applied and the sheet not filtered on any column the program will error. You need to add another condition in the test:
Code: 
With Worksheets("ActualData")
    If .AutoFilterMode Then
        If .FilterMode Then
            .ShowAllData
        End If
    End If
End With
If there is the possibility that your sheet may be filtered using Advanced Filter this code will need further expansion.

Hope this helps.
 
Upvote 0
Didn't see your last post before I posted. Will try to dig out the code I use for testing for Advanced Filter.
 
Upvote 0
Batman

The code works for me if the sheet isn't filtered.:o

But you are right about it not working when it is filtered and no filter has been applied.:)
 
Upvote 0
Advanced Filter sets the .FilterMode property to True while leaving the .AutoFilterMode property at False. Try:
Code: 
With Worksheets("ActualData")
    If .AutoFilterMode Then
        If .FilterMode Then
            .ShowAllData
        End If
    Else
        If .FilterMode Then
            .ShowAllData
        End If
    End If
End With
 
Upvote 0
Norie,

May not have explained myself correctly before. The .AutoFilterMode property tests for whether the filter drop-downs are displayed. The .FilterMode property tests for whether one or more filters are actually applied.

If you try to apply .ShowAllData when the .FilterMode property is False, VBA will error, which is why the test for .FilterMode first.

However, as per my last post, Advanced Filter leaves the .AutoFilterMode property at False (because the drop-downs are not displayed) but if filtered sets the .FilterMode property to True. Hence the need for the extra test.
 
Upvote 0
Batman

Thanks for the explanation.:)

I've not really looked into the AutoFilterMode and FilterMode properties too deeply.
 
Upvote 0
You must log in or register to reply here.

Similar threads

sparky2205
  • Solved
Loop increment help required
Replies
3
Views
366
rlv01
R
Rob_010101
  • Solved
Add message box
Replies
4
Views
497
Joe4
Joe4
gheyman
  • Solved
VBA to Remove filter on a table
Replies
3
Views
2K
johnnyL
J
C
  • Question Question
Find duplicates when pulling data from other sheets
Replies
3
Views
417
Trebor76
T
K
  • Question Question
Please help me make this code more efficient!
Replies
3
Views
405
kevin9999
K

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top