Is possible to uncompress all data if one or more column are filterd, via vba code, please...
Note:
Not eliminate the filter
uncompress all row if filtered column...
-
- PlatinumLounger
- Posts: 4370
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: uncompress all row if filtered column...
Barking up the wrong tree with the macro recorder I came up with
Hans's version is much easier
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
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
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
-
- PlatinumLounger
- Posts: 4370
- Joined: 26 Apr 2010, 17:36
Re: uncompress all row if filtered column...
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.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: uncompress all row if filtered column...
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:
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: uncompress all row if filtered column...
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
Hans