Deleting hidden rows and columns

[Nico12345]

Hi, I really hope someone can help me with this query. I'm a novice when it comes to VBA so be gentle.

I want to create a macro that will loop through ALL worksheets (the names of these are variable and not known in advance) EXCEPT one which is fixed (call it "FixedSheet"). I want the macro to delete all hidden rows and columns on all these sheets regardless of whether they have data. If they're hidden I want them deleted. Simple.

The code below worked once, but after saving and re-executing it no longer does, something about Select of Object Worksheet failed (runtime 1004). Granted this code does not exclude a worksheet but you get the gist.

Help very much appreciated.


Dim mysheet As Worksheet
For Each mysheet In Worksheets
mysheet.Select
For lp = 256 To 1 Step -1 'loop through all columns
If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Next
For lp = 65536 To 1 Step -1 'loop through all rows
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
Next

End Sub

 

[Jonmo1]

Welcome to the board..

It should work..
But rather than selecting the sheet, just add mysheet before each rows and columns reference..

You might also use Rows.Count and Columns.Count instead of hard coding the numbers, this makes it compatible with any version of Excel

And finally, to make it faster, try turning off Events, Calculation and Screenupdating

Try

Dim mysheet As Worksheet, lp As Long, PrevCalc As Variant
 
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    PrevCalc = .Calculation
    .Calculation = xlCalculationManual
End With
 
For Each mysheet In Worksheets
    For lp = Columns.Count To 1 Step -1 'loop through all columns
        If mysheet.Columns(lp).EntireColumn.Hidden = True Then mysheet.Columns(lp).EntireColumn.Delete
    Next lp
 
    For lp = Rows.Count To 1 Step -1 'loop through all rows
         If mysheet.Rows(lp).EntireRow.Hidden = True Then mysheet.Rows(lp).EntireRow.Delete
    Next
Next
 
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .Calculation = PrevCalc
End With

Hope that helps.

 

[Nico12345]

Hi, thanks that did the job. Just a couple of questions:

(1) It does seem to jam up even with your suggestion (taking up to a minute or so to run), is there any way of rewriting to make it speedier?

(2) How can I exclude specific sheets, say a fixed sheet called "FixedSheet" which I don't want the macro to do it's business on.

Thanks again.

 

[Jonmo1]

To make it a bit faster, you can restrict it to run only on your used range, instead of the ENTIRE sheet..

And use Select case to exclude specific sheets


Try

Dim mysheet As Worksheet, PrevCalc As Variant
Dim lp As Long, lc As Long, lr As Long
 
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    PrevCalc = .Calculation
    .Calculation = xlCalculationManual
End With
 
For Each mysheet In Worksheets
    Select Case mysheet.Name
        'Enter sheetnames to exclude here
        Case "Not This One", "Not this one either", "or this"
            'Do Nothing
        Case Else
            'Gets last used row# in column A
            lr = mysheet.Cells(Rows.Count, 1).End(xlUp).Row
 
            'Gets last used column# in row 1
            lc = mysheet.Cells(1, Columns.Count).End(xlToLeft).Column 
 
            For lp = lc To 1 Step -1 'loop through all columns
                If mysheet.Columns(lp).EntireColumn.Hidden = True Then mysheet.Columns(lp).EntireColumn.Delete
            Next lp
 
            For lp = lr To 1 Step -1 'loop through all rows
                If mysheet.Rows(lp).EntireRow.Hidden = True Then mysheet.Rows(lp).EntireRow.Delete
            Next
    End Select
Next
 
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .Calculation = PrevCalc
End With