Loop until
 

THANKS TO GARY BROWN for this awesome macro.
This is working but seems to need to be repeated to ensure that all hidden
rows are being deleted. Just one more tweek and it will be perfect.
How/where would you put a loop reference for just the Row (myrange)
subroutine?

'/======================================/
Public Sub FindHiddenAndLock()
Dim mCell As Range, MyRange As Range
Dim MyColumns As Range

On Error GoTo exit_Sub

Set MyRange = Range("A1:A20")
Set MyColumns = Range("A:E")

---------THIS IS THE ROW SUBROUTINE---------------------
For Each mCell In MyRange
If mCell.Rows.Hidden = True Then
Application.DisplayAlerts = False
mCell.EntireRow.Delete
Application.DisplayAlerts = True
End If
Next mCell

For Each mCell In MyColumns
If mCell.Columns.Hidden = True Then
Application.DisplayAlerts = False
mCell.EntireColumn.Delete
Application.DisplayAlerts = True
End If
Next mCell

exit_Sub:
On Error Resume Next
Application.DisplayAlerts = True
Set MyColumns = Nothing
Set MyRange = Nothing
Exit Sub

End Sub
'/======================================/

Loop until
 

Sub ABC()
Dim i as Long
for i = 20 to 1 step -1
if rows(i).Hidden then rows(i).Delete
Next
for i = 5 to 1 step -1
if columns(i).Hidden then columns(i).Delete
Next
End Sub


--
Regards,
Tom Ogilvy

"ttbbgg" wrote in message
...
THANKS TO GARY BROWN for this awesome macro.
This is working but seems to need to be repeated to ensure that all hidden
rows are being deleted. Just one more tweek and it will be perfect.
How/where would you put a loop reference for just the Row (myrange)
subroutine?

'/======================================/
Public Sub FindHiddenAndLock()
Dim mCell As Range, MyRange As Range
Dim MyColumns As Range

On Error GoTo exit_Sub

Set MyRange = Range("A1:A20")
Set MyColumns = Range("A:E")

---------THIS IS THE ROW SUBROUTINE---------------------
For Each mCell In MyRange
If mCell.Rows.Hidden = True Then
Application.DisplayAlerts = False
mCell.EntireRow.Delete
Application.DisplayAlerts = True
End If
Next mCell

For Each mCell In MyColumns
If mCell.Columns.Hidden = True Then
Application.DisplayAlerts = False
mCell.EntireColumn.Delete
Application.DisplayAlerts = True
End If
Next mCell

exit_Sub:
On Error Resume Next
Application.DisplayAlerts = True
Set MyColumns = Nothing
Set MyRange = Nothing
Exit Sub

End Sub
'/======================================/