VBA Unsort Show All Data

[Tuckejam]

One of the things I always try to do with my workbooks is to build a Macro button that re-writes all of the formulas and brings everything back to the way it was when it was opend (without affecting the data that has been entered into the workbook)

This way if someone really messes something up the can click the button instead of calling me!

Anyway the problem i am running to now is that i need to make sure the worksheet is unsorted before the macro rewrites all of the formulas.

So I use this

   Sheets("Assortment").Select
   ActiveSheet.ShowAllData

The problem is if the data has not been sorted in some way then i get an

Run -ime Error '1001':
ShowAllData method of worksheet class failed

anyone now a way to get around this.

maybee as simple as an

"if worksheet is sorted then ActiveSheet.ShowAllData"

but i dont know how to write that.

Thanks as always to everyone here. I am always amazed the the absolut brilliant solutions people help me with.

Thanks

 

[MARK858]

Doesn't ShowAllData apply to filter and not sort?
Anyway if it is the filter you mean then try something like...

  If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
            ActiveSheet.ShowAllData
        End If

 

[Tuckejam]

You are absolutly correct i was using the wrong word. I am refering to filtering.

Howver im still getting the same error when i tried that peice of code you sugested

Thanks again

 

[Tuckejam]

I got it i just wrote the macro to filter to something first then show all data.

Thanks again for you help though

 

[MARK858]

Try testing the code below to see if it does what you want

Sub TestFilt()
    Dim rngFilter As Range, rngF As Long, Frng As Long

    If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
        Set rngFilter = ActiveSheet.AutoFilter.Range
        rngF = rngFilter.Rows.Count
        Frng = rngFilter.SpecialCells(xlCellTypeVisible).Count

        If rngF > Frng Then ActiveSheet.ShowAllData
    End If

End Sub