Can someone make this macro work for the entire worksheet

[afierros]

Macro below - thanks for the help in advance.

Sub deleteHiddenColumnsRows()



Dim answer As Integer



answer = MsgBox("Ya sure? Hopefully you didn't hit this on accident", vbYesNo + vbQuestion, "Do eet")



If answer = vbYes Then



For lp = 256 To 1 Step -1
If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Next
For lp = 65536 To 1 Step -1
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next



Else
'nothing
End If



End Sub

 

[afierros]

please im desperate

 

[Alex Blakenburg]

This might be quite slow.
If you don't have hidden columns or rows beyond where you have data ie outside of the used range, we could limit the loop to run just on the used range which might make it a little quicker.

Sub deleteHiddenColumnsRows()

Dim answer As Integer
Dim lp As Long

answer = MsgBox("Ya sure? Hopefully you didn't hit this on accident", vbYesNo + vbQuestion, "Do eet")

If answer = vbYes Then
    
    For lp = ActiveSheet.Cells.Columns.Count To 1 Step -1
        If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
    Next
    
    For lp = ActiveSheet.Cells.Rows.Count To 1 Step -1
        If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
    Next

Else
    'nothing
End If

End Sub

 

[kevin9999]

Another option, although I quite like @Alex Blakenburg 's suggestion as well

Option Explicit
Sub deleteHiddenColumnsRows()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")                           '<-- *** Change to actual sheet name ***
    Dim answer As Integer, i As Long, c As Range, r As Range
    Dim LRow As Long, LCol As Long
    
    answer = MsgBox("Ya sure? Hopefully you didn't hit this on accident", vbYesNo + vbQuestion, "Do eet")
    If answer = vbYes Then
        LRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
        LCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
        
        With ws
            For i = 1 To LCol
                If .Columns(i).Hidden = True Then
                    If c Is Nothing Then Set c = .Columns(i) Else Set c = Union(c, .Columns(i))
                End If
            Next i
            If Not c Is Nothing Then c.EntireColumn.Delete
            For i = 1 To LRow
                If Rows(i).Hidden = True Then
                    If r Is Nothing Then Set r = Rows(i) Else Set c = Union(r, Rows(i))
                End If
            Next i
            If Not r Is Nothing Then r.EntireRow.Delete
        End With
    End If
End Sub