Rick - i will try this out, very unusual to say the least

Yes, I know the approach is "out of the box", but it is quite fast when
executing (Excel appears to be optimized "underneath it all" for
replacements and the SpecialCells function). While the code I posted
originally should work fine for you, I did leave out one statement that
should be in it to prevent an error from occurring just in case you chose to
run the code when there are no 1's in the data.

Sub HideRowsWithOnes()
With Columns("A")
.Replace 1, "=1", xlWhole
On Error Resume Next
.SpecialCells(xlCellTypeFormulas).EntireRow.Hidden = True
.Replace "=", "", xlPart
End With
End Sub


how would u do it if the cell contained a formula.

Same underlying method (just as fast though), but just a touch more work if
the data was produced by formulas as opposed being constant values.

Sub HideRowsWithOnes()
Dim StartRow As Long, LastRow As Long, UnusedColumn As Long
StartRow = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
Application.ScreenUpdating = False
Cells(StartRow, UnusedColumn).Resize(LastRow).Value = _
Cells(StartRow, "A").Resize(LastRow).Value
On Error Resume Next
With Columns(UnusedColumn)
.Replace "1", "", xlWhole
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
.EntireColumn.Clear
End With
Application.ScreenUpdating = True
End Sub

Note that with this method, it is necessary to specify the start row for the
data (headers, if any, are constants and must be stepped over). There is a
StartRow variable at the beginning of the code where you can specify this
value (I set it to 2 in my code assuming there was a header row... change if
necessary).

Rick Rothstein (MVP - Excel)