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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Is the sheet filtered using AutoFilter?
Code:
With Worksheets("ActualData")
    If .AutoFilterMode Then
        Worksheets("ActualData").ShowAllData
    End If
End With
 

mt

Board Regular
Joined
Feb 24, 2006
Messages
134
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
 

mt

Board Regular
Joined
Feb 24, 2006
Messages
134
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
 

BatmanUK

Board Regular
Joined
Jul 2, 2006
Messages
185

ADVERTISEMENT

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.
 

BatmanUK

Board Regular
Joined
Jul 2, 2006
Messages
185
Didn't see your last post before I posted. Will try to dig out the code I use for testing for Advanced Filter.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.:)
 

BatmanUK

Board Regular
Joined
Jul 2, 2006
Messages
185
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
 

BatmanUK

Board Regular
Joined
Jul 2, 2006
Messages
185
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Batman

Thanks for the explanation.:)

I've not really looked into the AutoFilterMode and FilterMode properties too deeply.
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,541
Latest member
freddyboots

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
Top