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
Next
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...
Regards,
Henry
Range("A1").Select
Range(Selection.End(xlRight))., Selection.End(xlDown)).Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("C:C").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="0"
Rows("1:800").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1
Selection.AutoFilter
