How to delete hidden rows in Workbook using Macros ?

[Dwij]

hello,

I want to delete some rows in a worksheet that are kept hidden. Is it possible to remove these hidden rows in a workbook using macros ? Can someone provide me the information regarding this ?

any ideas....

[JonathanD]

Yes, it's possible to delete rows in a workbook. Here is the macro to do the same -

Sub DeleteHiddenRows_Workbook() 

'This Microsoft Excel Macro will remove hidden rows from 
'all worksheets in a workbook.

For i = 1 To Worksheets.Count
If Worksheets(i).Visible Then
Worksheets(i).Select
ActiveCell.SpecialCells(xlLastCell).Select
k = ActiveCell.Row
For j = 1 To k
If Rows(j).Hidden Then
Rows(j).Hidden = False
Rows(j).Delete
End If
Next j
End If
Next i
If Worksheets(1).Visible Then Worksheets(1).Select

End Sub

[JonathanD]

Note - This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete one hidden row from a group at once. This means that if you have rows 3-9 hidden on a sheet and rows 45-55 hidden on the same sheet, the last hidden rows (row 9 and row 55) will be deleted on that worksheet. This means that the entire section of rows 3-9 etc. will not be deleted in one pass. Obviously if you are working with workbook that relies heavily on hidden data and hidden rows, this might not be the best macro.

[Macarenas]

Try this and check whether it works -

Sub deletehidden()

For lp = 1 To 256 'loop through all columns
If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Next

For lp = 1 To 65536 'loop through all rows
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
End Sub

[ThoMas321]

You can remove hidden rows from all the sheets -

Sub DeleteHiddenRows()
'
' Remove hidden rows from all sheets
'

For i = 1 To Worksheets.Count
If Worksheets(i).Visible Then
Worksheets(i).Select
ActiveCell.SpecialCells(xlLastCell).Select
k = ActiveCell.Row
For j = 1 To k
If Rows(j).Hidden Then
Rows(j).Hidden = False
Rows(j).Delete
End If
Next j
End If
Next i
If Worksheets(1).Visible Then Worksheets(1).Select

End Sub