ShowAllData

[mt]

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

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

 

[mt]

Thanks to both of you.

Mike

 

[BatmanUK]

I must admit it's something that I do almost automatically when I create a VBA project, especially where users have the ability to access and filter data before running the macro. I would generally create the macro with workbook and worksheet object parameters and unfilter any sheet that may potentially be filtered.

I've lost too much data using .End(xlUp) that finds the last visible row of data rather than the last populated row and overwritten existing data instead of going to the next available row!

 

[GJL]

Re: ShowAllData - Department of Redundancy Department?

Sorry to wake this thread up 7 years later, but isn't this:

With Worksheets("ActualData")
    If .AutoFilterMode Then
        If .FilterMode Then
            .ShowAllData
        End If
    Else
        If .FilterMode Then
            .ShowAllData
        End If
    End If
End With

The same as this?

With Worksheets("ActualData")
    If .FilterMode Then
        .ShowAllData
    End If
End With

AutoFilterMode is a boolean that can only be True or False. The first version checks FilterMode no matter what AutoFilterMode is set to. The second version also checks FilterMode no matter what AutoFilterMode is set to.

Am I missing something?


Thanks,

Greg

 

[Importer_Exporter]

Since we're waking up old threads, yes GJL I think you're right. .AutoFilterMode is only concerned with the dropdown filter arrows, .FilterMode is what really matters for .ShowAllData. The end result can be accomplished in one line:

If Sheet1.FilterMode Then Sheet1.ShowAllData