Actually, Peter T shows a much better method in the demo code (about my
Union method) that he posted elsewhere in this thread... simply execute this
single line of code to unhide every hidden cell (and leave the active cell
exactly where it currently is and with no selected ranges to deal with)...

Rows.Hidden = False

Rick


If the code you have now is working faster than mine, then keep it. You
should be able to unhide all the hidden rows with this single line...

Worksheets("Sheet1").Range("A:A").EntireRow.Hidden = False

After you do that, the last row or contiguous rows of previously hidden
rows will remain selected. You can either clear the selection like this...

Application.Selection.Clear

but that will leave the active cell at this "last row" which could be way
down on your worksheet. So, alternately, you can simply move the active
cell to a cell higher up in the worksheet; something like this...

Worksheets("Sheet1").Cells(1, 1).Select

I'm not sure if any of the above would be aided by turning off
ScreenUpdating beforehand and back on afterwards or not.

Rick


"Patrick C. Simonds" wrote in message
...
Your code works so well I am thinking for reverting back to it, since it
allows me to run a routine to unhide rows (which works equally fast) my
problem is this code does not work when there are formulas in row J. The
formulas in the cells return either a "" value or 1 of 3 text values (GPS
Error, Missing Perform Time, Negative Miles). Is there any way to make it
work with the formulas in column J?



"Rick Rothstein (MVP - VB)" wrote
in message ...
Yeah, I figured it might come out just a tad faster.<g Thanks for
carrying out the experiment for me.

Rick


"Patrick C. Simonds" wrote in message
...
It went from 8 minutes 42 seconds to only 4 seconds. I would say
that is a very dramatic improvement (with 65536 rows involved).




"Rick Rothstein (MVP - VB)" wrote
in message ...
I know you have a different solution now, but your comment about the
speed of the code I posted for you earlier got me to thinking. Probably
the speed problem is due to the continual hiding of the rows
one-by-one. I'm thinking the code below should be more efficient. If
you have the chance, I would be interested in how fast the code below
is compared to the code I gave you earlier.

Sub HideRowIfZeroInJ()
Dim R As Range
Dim RowsToHide As Range
Dim LastRow As Long
Application.ScreenUpdating = False
With Worksheets("Sheet3")
LastRow = .Cells(Rows.Count, "J").End(xlUp).Row
For Each R In .Range("J3:J" & CStr(LastRow))
If R.Value = 0 And R.Value < "" Then
If RowsToHide Is Nothing Then
Set RowsToHide = R
Else
Set RowsToHide = Union(R, RowsToHide)
End If
End If
Next
If Not RowsToHide Is Nothing Then RowsToHide.EntireRow.Hidden =
True
End With
Application.ScreenUpdating = True
End Sub

Rick



"Patrick C. Simonds" wrote in message
...
Thank you sir.

Having never used Filters before, I spent some time in the help files
learning how to use Filters, and I have to say it is much fast.



"Dave Peterson" wrote in message
...
Have you thought about just using Data|Filter|Autofilter the range
(column J)
and showing all the rows that don't have the cell in column J equal
to 0?

"Patrick C. Simonds" wrote:

We have a spreadsheet into which we dump a large amount of data
(averaging
around 55,000+ rows). We then use formulas to detect error in the
data so
that we can go back into the original program and correct those
errors. What
the code below does (my thanks to Rick Rothstein, MVP -VB) is hides
all rows
which do not have errors leaving only the rows with errors (saves
one from
having to scroll through more than 55,000 rows in search of
errors).

My problem is that it takes to long to hide the rows (in excess of
5
minutes). Does anyone have any thoughts on how to speed up the
process?

Sub HideRowIfZeroInG()
'
'
Application.ScreenUpdating = False

Dim R As Range
Dim LastRow As Long
With Worksheets("Negative Miles and Missing Perf")
LastRow = .Cells(Rows.Count, "J").End(xlUp).Row
If LastRow 65536 Then LastRow = 65536
For Each R In .Range("J3:J" & CStr(LastRow))
If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden =
True
Next
End With

Application.ScreenUpdating = True

End Sub

--

Dave Peterson