How do i remove hidden rows in an excel worksheet all at the same time?

Question

i am working on an excel spreadsheet and merging the data in word document. i dont need to include the data from hidden rows in the spreadsheet and when i do the merge, it merges all the data from the spreadsheet. help!

Answer 1

You might be tempted to try code like

Sub RemoveHiddenRows

Dim oRow As Object

For Each oRow In Sheets("Sheet1").Rows

If oRow.Hidden Then oRow.Delete

Next

End Sub

but that would only remove every other row of consecutive hidden rows because the each increments the row considered even when a row has been deleted and all lower rows have moved up one.

So you need to use code like

Sub RemoveHiddenRows(Target As Worksheet, MaxRow As Long)

Dim iRow As Long

iRow = 1

Do While iRow <= MaxRow

If Target.Rows(iRow).Hidden Then

Target.Rows(iRow).Delete

Else

iRow = iRow + 1

End If

Loop

End Sub

because iRow only gets incremented when the row is not deleted.  

You can eleiminate the parameters in the subroutine by changing Target to ActiveSheet (2 places) and changing MaxRow to ActiveSheet.Rows.Count or to a specifice value like 5000, if you ensure that the correct worksheet is active and any specific value is high enough.

If you don't know what to do with a subroutine, expand on your question to indicate that you need VBA macro help.