ShowAllData

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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.:eek:

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

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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