DELETE HIDDEN ROWS VIA MACRO

[esturges]

I have been given a worksheet (over 1600 rows) where detail rows were hidden so only totals show. I've now been asked to re-sort this worksheet and would like to delete the hidden rows as the detail has no relevance to the current use. I've changed all formulas to values so I can do this, but would like to automate the process as manual deletion is too time consuming. I came across the following macro, but it doesn't seem to work ... it appears to be working, but at the conclusion all hidden rows are still hidden and still part of the worksheet.

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

Any and all suggestions and/or corrections would be most appreciative as I'm "under the gun" (aren't we all?)!

TIA

P.S. I'm working in Excel 2000

 

[schwede]

If you just want the result: Download and install Asap Utilities (www.asap-utilties.com) Select Rows>Delete all hidden rows and columns.

 

[Datsmart]

You can also change this portion of your code...

Rows(j).Hidden = False
Rows(j).Delete

to...

Rows(j).EntireRow.Delete

 

[esturges]

Thanks for the quick replies! I tried changin the 2 lines of code to the revised one line ... that didn't work either.

I'm downloading the utility (initially couldn't find the page until I saw you had a typo in the address) and will give that a try!

Will post back.
Thanks, again to both of you for your suggestions!

 

[schwede]

If you don't get it to work, try this code:

Sub DeleteHiddenRows()


For Each ws In Worksheets
    
    For i = ws.Range("A:IV").End(xlDown).Row To 1 Step -1
        If ws.Rows(i).Hidden = True Then ws.Rows(i).EntireRow.Delete
    
    Next i
Next ws

End Sub

 

[esturges]

Schwede! What a jewel of an add-in you turned me onto!! OMG! Columns and rows gone in the blink of an eye! If this isn't in every Excel "toolbox", it should be!! I can't wait to see what else this little gem can do!

Thanks a million! You just made my day!
Yaaaa-HOO!!!

 

[schwede]

Don't thank me. I didn't write it!

Take a look at this: Excel Add-Ins

There is some pretty nice stuff in there...

Cheers,
Schwede