How can I delete rows that are hidden.

  • Thread starter Thread starter Guest
  • Start date Start date
Unhide them first.
Select the row before and the row after the hidden ones, and use
Format > Row > Unhide.
 
one way:

Public Sub DeleteHiddenRows()
Dim rDelete As Range
Dim rCell As Range

For Each rCell In ActiveSheet.UsedRange.Columns(1).Cells
If rCell.EntireRow.Hidden Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub


Raquel G. <[email protected]> wrote:
 
Alternative method if there are no formulas that would be affected.

CRTL + A to select all cells or just select your data range.

Edit>Go To>Special>Visible Cells Only>OK

Edit>Copy

Insert>Worksheet>Paste, which pastes all but the Hidden rows.

Delete the original sheet.

Gord Dibben XL2002
 
One approach:
1) Edit>GoTo. Click "Special". Select "Visible Cells Only". Click OK
2) Copy the selection you just generated
3) Paste it onto a new, empty sheet (or PasteSpecial>Values if appropriate)

This will give you a new sheet with everything that was visible (and only
that which was visible) on the old sheet. You can then delete the old sheet
if desired (and if formulas haven't been broken).
Note that this method can be applied to filtered lists, outlines, etc. as
well.

Hope this helps,
 
Back
Top