uncompress all row if filtered column...

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

uncompress all row if filtered column...

Post by sal21 »

Is possible to uncompress all data if one or more column are filterd, via vba code, please...

Note:
Not eliminate the filter

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: uncompress all row if filtered column...

Post by HansV »

ActiveSheet,ShowAllData
Best wishes,
Hans

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: uncompress all row if filtered column...

Post by steveh »

Barking up the wrong tree with the macro recorder I came up with

Code: Select all

Sub UnFilter()
'
' UnFilter Macro
' Macro recorded 28/07/2014 by steve
'

'
    Selection.AutoFilter Field:=1
    Selection.AutoFilter Field:=2
    Selection.AutoFilter Field:=3
    Selection.AutoFilter Field:=4
    Selection.AutoFilter Field:=5
    Selection.AutoFilter Field:=6
    Selection.AutoFilter Field:=7
    Selection.AutoFilter Field:=8
    Selection.AutoFilter Field:=9
    Selection.AutoFilter Field:=10
    Selection.AutoFilter Field:=11
    Selection.AutoFilter Field:=12
    Selection.AutoFilter Field:=13
    Selection.AutoFilter Field:=14
    Selection.AutoFilter Field:=15
    Selection.AutoFilter Field:=16
    Selection.AutoFilter Field:=17
    Range("R2").Select
    Selection.AutoFilter Field:=18
    Selection.AutoFilter Field:=19
    Selection.AutoFilter Field:=20
    Application.CommandBars("Stop Recording").Visible = False
End Sub
Hans's version is much easier :grin:
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: uncompress all row if filtered column...

Post by sal21 »

HansV wrote:ActiveSheet,ShowAllData

OPS...

....

Sheets("TEMPLATE").Select
Sheets("TEMPLATE").ShowAllData<<<<< error here
....

See image attached.
You do not have the required permissions to view the files attached to this post.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: uncompress all row if filtered column...

Post by Rudi »

There is probably no filter active on the sheet. ShowAllData will debug if not filter is applied and you run the method.
Modify the code to something like this:

Code: Select all

On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
Last edited by Rudi on 29 Jul 2014, 15:05, edited 1 time in total.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: uncompress all row if filtered column...

Post by HansV »

You'll get that error if the sheet is not filtered. You could use

Code: Select all

    With Worksheets("TEMPLATE")
        .Select
        If .FilterMode Then
            .ShowAllData
        End If
    End With
Best wishes,
Hans