Find Hidden rows and delete
 

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
***

Find Hidden rows and delete
 

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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
***

Find Hidden rows and delete
 

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).
=)

"Gary Brown" wrote:

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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
***

Find Hidden rows and delete
 

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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).
=)

"Gary Brown" wrote:

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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
***

Find Hidden rows and delete
 

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?

"Gary Brown" wrote:

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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).
=)

"Gary Brown" wrote:

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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
***

Find Hidden rows and delete
 

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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?

"Gary Brown" wrote:

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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).
=)

"Gary Brown" wrote:

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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
***

Find Hidden rows and delete
 

Awesome, thanks. This works great.

"Gary Brown" wrote:

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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?

"Gary Brown" wrote:

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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).
=)

"Gary Brown" wrote:

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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
***

Find Hidden rows and delete
 

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?

"ttbbgg" wrote:

Awesome, thanks. This works great.

"Gary Brown" wrote:

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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?

"Gary Brown" wrote:

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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).
=)

"Gary Brown" wrote:

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

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"ttbbgg" wrote:

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
***