ShowAllData Method Returns Error

Hi all,

Using Excel 2013 64-bit.
I recrded a bit of code while clearing a filter.

The resulting code gave me the ShowAllData Method
So I tried using in my procedure but I am receiving an error:

Run-time error '1004':
ShowAllData Method of Worksheet Class Failed

Any idea what I am doing wrong?
Thanks
w

avSheets = Array("Control", "Work", "Work2")
        
        'Make sure autofilter is off
        'Make sure all rows are visible
        'Clear previous data from sheets that hold temporary data
            For l = 1 To UBound(avSheets)
                With wb
                    Set ws = .Worksheets(avSheets(l))
                    With ws
                        .Activate
                        With ActiveSheet
                            .ShowAllData
                        End With
                        .AutoFilterMode = False
                        .Select
                        With Selection
                            .EntireRow.Hidden = False
                        End With
                        .UsedRange.ClearContents
                    End With
                End With
            Next l

Hi, goss,

your code say

'Make sure autofilter is off

Maybe you check for an Autofilter prior to that command by

If .AutofilterMode Then
    .ShowAllData
End If

Ciao,
Holger

Thanks Holger,

I revised the code slightly at your suggestion.
Now receiving this error message

Run-time error '1004':
This command requires at least two rows of source data. you cannot use the command on a selection in only one row. Try the following...

Thanks
w

Revised:

avSheets = Array("Control", "Work", "Work2")
        
        'Make sure autofilter is off
        'Make sure all rows are visible
        'Clear previous data from sheets that hold temporary data
            For l = 1 To UBound(avSheets)
                With wb
                    Set ws = .Worksheets(avSheets(l))
                    With ws
                        If .AutoFilterMode Then
                            .ShowAllData
                            .AutoFilterMode = False
                        End If
'                        .Activate
'                        With ActiveSheet
'                            .ShowAllData
'                        End With
                        .Select
                        With Selection
                            .EntireRow.Hidden = False
                        End With
                        .UsedRange.ClearContents
                    End With
                End With
            Next l

Hi, goss,

maybe like this:

avSheets = Array("Control", "Work", "Work2")
        
'Make sure autofilter is off
'Make sure all rows are visible
'Clear previous data from sheets that hold temporary data
For l = 1 To UBound(avSheets)
    With wb
        Set ws = .Worksheets(avSheets(l))
        With ws
            If .AutoFilterMode Then
                .ShowAllData
                .AutoFilterMode = False
            End If
            .Cells.EntireRow.Hidden = False
            .UsedRange.ClearContents
        End With
    End With
Next l

Ciao,
Holger

Thanks Holger,

Same error.
It's something I've coded futher down that creates the filter and hidden rows, but I do not understand why the code above does not handle it?
ExportToPDF2 contains the updated code

Thanks
w

Hi, goss,

you clear the contents on the sheet Control, you put in headers in the first line and then you want to execute an advanced filter on data not present - to me the run time error is understandable as I donīt know why you want to do that. Workaround would be to check the last filled line and only execute the filerting if the number of filled rows is at least 3 or greater.

Ciao,
Holger

Thanks Holger,

The problem was here:

  If .AutoFilterMode Then
                .ShowAllData
                .AutoFilterMode = False

Should be:

  If .FilterMode Then
                .ShowAllData

Since I am using advanced filter.

Thanks for your help
Kind regards,
w