How can I delete hidden rows after filter has been set?

[pensix]

Hi,
I currently have a workbook with multiple sheets that have a macro running to set a filter based on specific criteria from the main sheet. I would like to be able to have all items that are not a part of the filter (hidden) be deleted from the supporting sheets in an effort to reduce the size of the file. Here is the VBA code that I am presently running to set the filters. How can this code be modified to delete the non essential rows from the secondary tabs?
The current code is:

Sub Update()
'
' Update Macro
'
' Keyboard Shortcut: Ctrl+Shift+U
'
Sheets("AR 105 0000 Sub Account ").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Range("A5:AN10000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A3:AN4"), Unique:=False
Sheets(" TOTAL 6200 Sub Account ").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Range("A5:T163").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A3:T4"), Unique:=False
Sheets("AP 204 0000 Sub Account").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Range("A5:AF10000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A3:AF4"), Unique:=False
Sheets("AP 204 6125 Sub Account").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Range("A5:V184").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A3:U4"), Unique:=False
Sheets("SUMMARY").Select
Range("D3").Select
ActiveCell.FormulaR1C1 = "UPDATE COMPLETED"
Range("B13").Select
End Sub

Thank you in advance!

 

[Mister H]

Hi

TRY THIS ON A COPY OF YOUR SPREADSHET

[I]
Sub hiddendelete()[/I]
[I]For lp = 256 To 1 Step -1 'loop through all columns[/I]
[I]If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else[/I]
[I]Next[/I]
[I]For lp = 65536 To 1 Step -1 'loop through all rows[/I]
[I]If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else[/I]
[I]Next[/I]
[I]End Sub
[/I]

Just reference this macro in your other macro

Call hiddendelete

Good Luck
Mark

 

[pensix]

Hi, thank you for your feedback. I've tried to include the code above however it only runs on the main sheet. How can I get this to run only on the sheets that are named "AR 105 0000 Sub Account" and "AP 204 0000 Sub Account"?

 

[Mister H]

Hi

If you just want it to run on those two sheets "AR 105 0000 Sub Account" and "AP 204 0000 Sub Account" then try this:

Sub Update()
 '
 ' Update Macro
 '
 ' Keyboard Shortcut: Ctrl+Shift+U
 '

Application.ScreenUpdating = False


     Sheets("AR 105 0000 Sub Account ").Select
     Application.CutCopyMode = False
     Application.CutCopyMode = False
     Range("A5:AN10000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
         Range("A3:AN4"), Unique:=False
[I]Call hiddendelete[/I]


Sheets(" TOTAL 6200  Sub Account  ").Select
     Application.CutCopyMode = False
     Application.CutCopyMode = False
     Range("A5:T163").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
         Range("A3:T4"), Unique:=False

     Sheets("AP 204 0000 Sub Account").Select
     Application.CutCopyMode = False
     Application.CutCopyMode = False
     Range("A5:AF10000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
         Range("A3:AF4"), Unique:=False
Call [I]hiddendelete[/I]

Sheets("AP 204 6125  Sub Account").Select
     Application.CutCopyMode = False
     Application.CutCopyMode = False
     Range("A5:V184").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
         Range("A3:U4"), Unique:=False
     Sheets("SUMMARY").Select
     Range("D3").Select
     ActiveCell.FormulaR1C1 = "UPDATE COMPLETED"
     Range("B13").Select

Application.ScreenUpdating = True

 End Sub

[I]Sub hiddendelete()[/I]
[I]For lp = 256 To 1 Step -1 'loop through all columns[/I]
[I]If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else[/I]
[I]Next[/I]
[I]For lp = 65536 To 1 Step -1 'loop through all rows[/I]
[I]If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else[/I]
[I]Next[/I]
[I]End Sub[/I]

Jus call the delete macro while the sheet is active. Also if you turn off Screenupdating the codes run quicker. IT MUST BE TURNED BACK ON AT THE END.

Good Luck,
Mark