VBA - For Next Statement

Posted by Mark on November 06, 2001 4:19 PM

I need to write a for next statment that will allow me to scroll through row numbers and allow me to hide a row if the value in column 1 of that row is 0. Any help?

Thanks

Posted by b on November 06, 2001 5:10 PM

for i=1 to range("A65536").end(xlup).row
if cells(i,1).value=0 then rows(i).hidden=true
Next

Posted by Juan Pablo on November 06, 2001 5:36 PM

Try with this macro

Sub Hidezeros()
Dim Rng As Range
Columns("A:A").Insert Shift:=xlToRight
Set Rng = Range("B2", Range("B65536").End(xlUp))
Rng.Offset(, -1).Select
Selection.FormulaR1C1 = "=IF(RC[1]=0,1,"""")"
Selection.SpecialCells(xlCellTypeFormulas, 1).Select
Selection.EntireRow.Hidden = True
Columns("A:A").Delete Shift:=xlToLeft
Range("A1").Select
End Sub

Juan Pablo

Posted by . on November 07, 2001 1:41 AM

Or ...

....... to tidy it up a bit (and make faster) :-

Sub Hidezeros()
Dim Rng As Range
Application.ScreenUpdating = False
Columns(1).Insert
Set Rng = Range("B2", Range("B65536").End(xlUp)).Offset(, -1)
With Rng
.FormulaR1C1 = "=IF(RC[1]=0,1,"""")"
.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Hidden = True
.EntireColumn.Delete
End With
End Sub