MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to hide rows

Posted by Angella on January 23, 2002 9:07 AM

I need to make a macro that will not only copy and paste info from one sheet to another but also hide the rows with a zero value on the pasted page. I need to paste about 1000 lines of data onto a seperate worksheet and I only want 700 or so to show up on the pasted page because the balance of the lines are O. Any ideas?

Posted by Tom Dickinson on January 23, 2002 10:08 AM

Not sure how you want to copy (as a block or line by line), you may want to turn the macro recorder on and then perform the copy, turn the recorder off and edit the macro as necessary.

As for the line hiding, assuming the test is in column "C", then the macro would look something like this:

Sub RwHide ()
Dim RwCnt as integer
For RwCnt = 2 to 1000
If Range("C" & RwCnt) = 0 Then
Range("C" & RwCnt).EntireRow.Hidden = True
End Sub

Posted by Henry Collet on January 23, 2002 10:18 AM

Do it as a four step process: copy the cells, filter for 0's, delete and then reveal all. Code is below. Change A1 to where the begining of the data is (add worksheet reference). Change C1 to where you want the data (add worksheet location). Change the C:C to the column the 0's are in et voila...


Range(Selection.End(xlRight))., Selection.End(xlDown)).Select
Selection.AutoFilter Field:=1, Criteria1:="0"
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1