G
Guest
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
'/======================================/
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
'/======================================/