Find Hidden rows and delete

[Guest]

I was given a macro by Sandy here which is not working exactly the way I
need. I need to be able to seek out first all rows that are Hidden and have
them deleted. When a row is deleted, the unhidden rows should move up. Then
I need the macro to look for hidden columns and do the same.

Issue with this macro: Since this is an mcell reference, it is only
deleting cells, I need entire rows, then columns deleted.
***
Sub FindHiddenAndLock()
Dim mcell, MyRange As Range
Set MyRange = Range("A1:A10")
For Each mcell In MyRange
If mcell.Rows.Hidden = True Then
mcell.Rows.Delete
End If
Next
End Sub
***

 

[Guest]

it's only deleting the cell because you've Set MyRange = Range("A1:A10")
instead of A1:C10 or whatever. To get around it, change...
mcell.Rows.Delete
to
mcell.EntireRow.Delete

Here's a full 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")

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

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[Guest]

O wow this is great. Just one more thing. The rows don't seem to be
deleting properly and I suspect I'll have to delete the rows from the bottom
and go up in direction. What is the step command to start from the end of
the range and go upwards (only for the Rows sub).
=)

 

[Guest]

Dim i As Long
For i = 20 To 1 Step -1
If Rows(i).Hidden = True Then
Application.DisplayAlerts = False
Rows(i).EntireRow.Delete
Application.DisplayAlerts = True
End If
Next i

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[Guest]

Having trouble. Where do I insert this? Do I replace the mcell or MyRange
references? Perhaps you could repost the entire script with the new dim i
references?

 

[Guest]

'/======================================/
Public Sub FindHiddenAndLock()
Dim i As Long
Dim mCell As Range
Dim MyColumns As Range

On Error GoTo exit_Sub

Set MyColumns = Range("A:E")

For i = 20 To 1 Step -1
If Rows(i).Hidden = True Then
Application.DisplayAlerts = False
Rows(i).EntireRow.Delete
Application.DisplayAlerts = True
End If
Next i

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

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[Guest]

Awesome, thanks. This works great.

'/======================================/
Public Sub FindHiddenAndLock()
Dim i As Long
Dim mCell As Range
Dim MyColumns As Range

On Error GoTo exit_Sub

Set MyColumns = Range("A:E")

For i = 20 To 1 Step -1
If Rows(i).Hidden = True Then
Application.DisplayAlerts = False
Rows(i).EntireRow.Delete
Application.DisplayAlerts = True
End If
Next i

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

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[Guest]

I have one more issue with this...sorry.
It seems like no matter what direction, up or down, not all hidden rows get
deleted. Example: There are 3 rows hidden consecutively, the macro finds
the first row and deletes it, thus pushing the second row up, and then the
original third row is deleted. Because the second hidden row was pushed up,
the macro missed it on the find sweep. Can we add a "loop until" command so
that it keeps sweeping the sheet until there are NO hidden rows?