Code runs to slow

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
Sat Mar 15 18:04:41 CDT 2008

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

Patrick
Sat Mar 15 20:33:10 CDT 2008

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" <petersod@verizonXSPAM.net> wrote in message
news:47DC5609.795F916B@verizonXSPAM.net...
> 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

Rick
Sat Mar 15 22:49:56 CDT 2008

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" <ordnance1@comcast.net> wrote in message
news:%23xNJ1WwhIHA.5504@TK2MSFTNGP05.phx.gbl...
> 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" <petersod@verizonXSPAM.net> wrote in message
> news:47DC5609.795F916B@verizonXSPAM.net...
>> 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
>

Patrick
Sun Mar 16 01:34:16 CDT 2008

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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:uPCo$ixhIHA.3512@TK2MSFTNGP03.phx.gbl...
>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" <ordnance1@comcast.net> wrote in message
> news:%23xNJ1WwhIHA.5504@TK2MSFTNGP05.phx.gbl...
>> 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" <petersod@verizonXSPAM.net> wrote in message
>> news:47DC5609.795F916B@verizonXSPAM.net...
>>> 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
>>
>

Rick
Sun Mar 16 01:55:37 CDT 2008

Yeah, I figured it might come out just a tad faster.<g> Thanks for carrying
out the experiment for me.

Rick


"Patrick C. Simonds" <ordnance1@comcast.net> wrote in message
news:%235eqG$yhIHA.5260@TK2MSFTNGP03.phx.gbl...
> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
> message news:uPCo$ixhIHA.3512@TK2MSFTNGP03.phx.gbl...
>>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" <ordnance1@comcast.net> wrote in message
>> news:%23xNJ1WwhIHA.5504@TK2MSFTNGP05.phx.gbl...
>>> 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" <petersod@verizonXSPAM.net> wrote in message
>>> news:47DC5609.795F916B@verizonXSPAM.net...
>>>> 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
>>>
>>
>

Patrick
Sun Mar 16 02:12:47 CDT 2008

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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:e%23s7vKzhIHA.4320@TK2MSFTNGP06.phx.gbl...
> Yeah, I figured it might come out just a tad faster.<g> Thanks for
> carrying out the experiment for me.
>
> Rick
>
>
> "Patrick C. Simonds" <ordnance1@comcast.net> wrote in message
> news:%235eqG$yhIHA.5260@TK2MSFTNGP03.phx.gbl...
>> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote
>> in message news:uPCo$ixhIHA.3512@TK2MSFTNGP03.phx.gbl...
>>>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" <ordnance1@comcast.net> wrote in message
>>> news:%23xNJ1WwhIHA.5504@TK2MSFTNGP05.phx.gbl...
>>>> 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" <petersod@verizonXSPAM.net> wrote in message
>>>> news:47DC5609.795F916B@verizonXSPAM.net...
>>>>> 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
>>>>
>>>
>>
>

Rick
Sun Mar 16 03:35:34 CDT 2008

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" <ordnance1@comcast.net> wrote in message
news:OOmDoUzhIHA.5780@TK2MSFTNGP06.phx.gbl...
> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
> message news:e%23s7vKzhIHA.4320@TK2MSFTNGP06.phx.gbl...
>> Yeah, I figured it might come out just a tad faster.<g> Thanks for
>> carrying out the experiment for me.
>>
>> Rick
>>
>>
>> "Patrick C. Simonds" <ordnance1@comcast.net> wrote in message
>> news:%235eqG$yhIHA.5260@TK2MSFTNGP03.phx.gbl...
>>> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote
>>> in message news:uPCo$ixhIHA.3512@TK2MSFTNGP03.phx.gbl...
>>>>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" <ordnance1@comcast.net> wrote in message
>>>> news:%23xNJ1WwhIHA.5504@TK2MSFTNGP05.phx.gbl...
>>>>> 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" <petersod@verizonXSPAM.net> wrote in message
>>>>> news:47DC5609.795F916B@verizonXSPAM.net...
>>>>>> 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
>>>>>
>>>>
>>>
>>
>

Peter
Sun Mar 16 06:45:37 CDT 2008

One thing to watch out for with Union is that it becomes exponentially
slower if/as the number of discontiguous areas in the unioned range
increase. Typically it's faster to process batches of 50-400 areas at a
time. The exact qty of areas depends on what the process is but a good rule
of thumb is limit to about 80-100.

The following should demonstrate the difference, trying to union 20k areas
is not viable.

Sub UnionDemo()
Dim bFastTest As Boolean
Dim bGotRng As Boolean
Dim i As Long
Dim arr() As Long
Dim rHide As Range

Rows.Hidden = False
Columns(1).Clear

Set rng = Range("A1:A40000")

ReDim arr(1 To rng.Rows.Count, 1 To 1)
For i = 1 To UBound(arr) Step 2
arr(i, 1) = 1
Next

rng.Value = arr

bFastTest = True
' bFastTest = False ' reduce rng size above to say 4k or less for
testing

t = Timer ' sensitive timer not required !

If bFastTest Then

For Each cell In rng
If cell.Value = 0 Then
If bGotRng Then
Set rHide = Union(rHide, cell)
If rHide.Areas.Count > 100 Then
rHide.EntireRow.Hidden = True
Set rHide = Nothing
bGotRng = False
End If
Else
Set rHide = cell
bGotRng = True
End If
End If
Next

If Not rHide Is Nothing Then ' or If bGotRng ...
rHide.EntireRow.Hidden = True
End If

Else ' do ctrl-break if it's taking to long
Set rHide = Nothing
For Each cell In rng
If cell.Value = 0 Then
If rHide Is Nothing Then
Set rHide = cell
Else
Set rHide = Union(rHide, cell)
End If
End If
Next

If Not rHide Is Nothing Then
rHide.EntireRow.Hidden = True
End If

End If
Debug.Print Timer - t

End Sub

Regards,
Peter T


"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:uPCo$ixhIHA.3512@TK2MSFTNGP03.phx.gbl...
> 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
>
<snip>

Rick
Sun Mar 16 11:26:15 CDT 2008

I got a difference of 1.18359375 seconds when I ran your code; but I have a
pretty fast computer here, so that time differential may not be
representative. In any case, point taken... limit the unionizing to groups
of 100 or so... even doing that should still produce an enormous improvement
over doing it in the straight forward, one-at-a-time iteration approach.
Thanks for pointing that out.

Rick


"Peter T" <peter_t@discussions> wrote in message
news:edwgQt1hIHA.6084@TK2MSFTNGP06.phx.gbl...
> One thing to watch out for with Union is that it becomes exponentially
> slower if/as the number of discontiguous areas in the unioned range
> increase. Typically it's faster to process batches of 50-400 areas at a
> time. The exact qty of areas depends on what the process is but a good
> rule
> of thumb is limit to about 80-100.
>
> The following should demonstrate the difference, trying to union 20k areas
> is not viable.
>
> Sub UnionDemo()
> Dim bFastTest As Boolean
> Dim bGotRng As Boolean
> Dim i As Long
> Dim arr() As Long
> Dim rHide As Range
>
> Rows.Hidden = False
> Columns(1).Clear
>
> Set rng = Range("A1:A40000")
>
> ReDim arr(1 To rng.Rows.Count, 1 To 1)
> For i = 1 To UBound(arr) Step 2
> arr(i, 1) = 1
> Next
>
> rng.Value = arr
>
> bFastTest = True
> ' bFastTest = False ' reduce rng size above to say 4k or less for
> testing
>
> t = Timer ' sensitive timer not required !
>
> If bFastTest Then
>
> For Each cell In rng
> If cell.Value = 0 Then
> If bGotRng Then
> Set rHide = Union(rHide, cell)
> If rHide.Areas.Count > 100 Then
> rHide.EntireRow.Hidden = True
> Set rHide = Nothing
> bGotRng = False
> End If
> Else
> Set rHide = cell
> bGotRng = True
> End If
> End If
> Next
>
> If Not rHide Is Nothing Then ' or If bGotRng ...
> rHide.EntireRow.Hidden = True
> End If
>
> Else ' do ctrl-break if it's taking to long
> Set rHide = Nothing
> For Each cell In rng
> If cell.Value = 0 Then
> If rHide Is Nothing Then
> Set rHide = cell
> Else
> Set rHide = Union(rHide, cell)
> End If
> End If
> Next
>
> If Not rHide Is Nothing Then
> rHide.EntireRow.Hidden = True
> End If
>
> End If
> Debug.Print Timer - t
>
> End Sub
>
> Regards,
> Peter T
>
>
> "Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
> message news:uPCo$ixhIHA.3512@TK2MSFTNGP03.phx.gbl...
>> 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
>>
> <snip>
>
>

Rick
Sun Mar 16 11:50:17 CDT 2008

Ignore what I said about my timing... I misunderstood your posting and
thought your code measured the time-difference in a single run... I didn't
realize it was a two-step process requiring the bFastTest be flipped
manually for the two cases... the time I reported was the time it took to
run the "chunk" method. I had to break into the "non-chunk" method as it was
taking too long. Again, thanks for pointing this problem out. By the way,
for those out there following this thread, the reason all this is important
is the Union technique has application to deleting rows of data (a
relatively common question in these groups) and the apparently recommended
method is to iterate the data rows, from the last data row to the first,
deleting rows as you go... this Union technique (still requiring the
backward iterations if using Peter T's "chunk" method) promises to be an
enormously quicker approach, so it is important to fine-tune it as much as
is possible.

Rick


"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:%23zaa1J4hIHA.1188@TK2MSFTNGP04.phx.gbl...
>I got a difference of 1.18359375 seconds when I ran your code; but I have a
>pretty fast computer here, so that time differential may not be
>representative. In any case, point taken... limit the unionizing to groups
>of 100 or so... even doing that should still produce an enormous
>improvement over doing it in the straight forward, one-at-a-time iteration
>approach. Thanks for pointing that out.
>
> Rick
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:edwgQt1hIHA.6084@TK2MSFTNGP06.phx.gbl...
>> One thing to watch out for with Union is that it becomes exponentially
>> slower if/as the number of discontiguous areas in the unioned range
>> increase. Typically it's faster to process batches of 50-400 areas at a
>> time. The exact qty of areas depends on what the process is but a good
>> rule
>> of thumb is limit to about 80-100.
>>
>> The following should demonstrate the difference, trying to union 20k
>> areas
>> is not viable.
>>
>> Sub UnionDemo()
>> Dim bFastTest As Boolean
>> Dim bGotRng As Boolean
>> Dim i As Long
>> Dim arr() As Long
>> Dim rHide As Range
>>
>> Rows.Hidden = False
>> Columns(1).Clear
>>
>> Set rng = Range("A1:A40000")
>>
>> ReDim arr(1 To rng.Rows.Count, 1 To 1)
>> For i = 1 To UBound(arr) Step 2
>> arr(i, 1) = 1
>> Next
>>
>> rng.Value = arr
>>
>> bFastTest = True
>> ' bFastTest = False ' reduce rng size above to say 4k or less for
>> testing
>>
>> t = Timer ' sensitive timer not required !
>>
>> If bFastTest Then
>>
>> For Each cell In rng
>> If cell.Value = 0 Then
>> If bGotRng Then
>> Set rHide = Union(rHide, cell)
>> If rHide.Areas.Count > 100 Then
>> rHide.EntireRow.Hidden = True
>> Set rHide = Nothing
>> bGotRng = False
>> End If
>> Else
>> Set rHide = cell
>> bGotRng = True
>> End If
>> End If
>> Next
>>
>> If Not rHide Is Nothing Then ' or If bGotRng ...
>> rHide.EntireRow.Hidden = True
>> End If
>>
>> Else ' do ctrl-break if it's taking to long
>> Set rHide = Nothing
>> For Each cell In rng
>> If cell.Value = 0 Then
>> If rHide Is Nothing Then
>> Set rHide = cell
>> Else
>> Set rHide = Union(rHide, cell)
>> End If
>> End If
>> Next
>>
>> If Not rHide Is Nothing Then
>> rHide.EntireRow.Hidden = True
>> End If
>>
>> End If
>> Debug.Print Timer - t
>>
>> End Sub
>>
>> Regards,
>> Peter T
>>
>>
>> "Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote
>> in
>> message news:uPCo$ixhIHA.3512@TK2MSFTNGP03.phx.gbl...
>>> 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
>>>
>> <snip>
>>
>>
>

Peter
Sun Mar 16 12:03:22 CDT 2008

Was that making a union of 2k areas (loop of 4000 cells) or 20k areas (40k
cells). If the if the latter I can only assume your machine runs on a
combination of nitrous-oxide and steroids. For my curiosity how long for you
to run this -

Sub SteroidTest()
Dim i As Long
Dim t As Single
Dim rng As Range

t = Timer

For i = 1 To 40000 Step 2
If rng Is Nothing Then
Set rng = Cells(i, 1)
Else
Set rng = Union(rng, Cells(i, 1))
End If
Next
Debug.Print Timer - t ' ?
Debug.Print rng.Areas.Count ' 20,000

End Sub

Regards,
Peter T


"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:%23zaa1J4hIHA.1188@TK2MSFTNGP04.phx.gbl...
> I got a difference of 1.18359375 seconds when I ran your code; but I have
a
> pretty fast computer here, so that time differential may not be
> representative. In any case, point taken... limit the unionizing to groups
> of 100 or so... even doing that should still produce an enormous
improvement
> over doing it in the straight forward, one-at-a-time iteration approach.
> Thanks for pointing that out.
>
> Rick
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:edwgQt1hIHA.6084@TK2MSFTNGP06.phx.gbl...
> > One thing to watch out for with Union is that it becomes exponentially
> > slower if/as the number of discontiguous areas in the unioned range
> > increase. Typically it's faster to process batches of 50-400 areas at a
> > time. The exact qty of areas depends on what the process is but a good
> > rule
> > of thumb is limit to about 80-100.
> >
> > The following should demonstrate the difference, trying to union 20k
areas
> > is not viable.
> >
> > Sub UnionDemo()
> > Dim bFastTest As Boolean
> > Dim bGotRng As Boolean
> > Dim i As Long
> > Dim arr() As Long
> > Dim rHide As Range
> >
> > Rows.Hidden = False
> > Columns(1).Clear
> >
> > Set rng = Range("A1:A40000")
> >
> > ReDim arr(1 To rng.Rows.Count, 1 To 1)
> > For i = 1 To UBound(arr) Step 2
> > arr(i, 1) = 1
> > Next
> >
> > rng.Value = arr
> >
> > bFastTest = True
> > ' bFastTest = False ' reduce rng size above to say 4k or less for
> > testing
> >
> > t = Timer ' sensitive timer not required !
> >
> > If bFastTest Then
> >
> > For Each cell In rng
> > If cell.Value = 0 Then
> > If bGotRng Then
> > Set rHide = Union(rHide, cell)
> > If rHide.Areas.Count > 100 Then
> > rHide.EntireRow.Hidden = True
> > Set rHide = Nothing
> > bGotRng = False
> > End If
> > Else
> > Set rHide = cell
> > bGotRng = True
> > End If
> > End If
> > Next
> >
> > If Not rHide Is Nothing Then ' or If bGotRng ...
> > rHide.EntireRow.Hidden = True
> > End If
> >
> > Else ' do ctrl-break if it's taking to long
> > Set rHide = Nothing
> > For Each cell In rng
> > If cell.Value = 0 Then
> > If rHide Is Nothing Then
> > Set rHide = cell
> > Else
> > Set rHide = Union(rHide, cell)
> > End If
> > End If
> > Next
> >
> > If Not rHide Is Nothing Then
> > rHide.EntireRow.Hidden = True
> > End If
> >
> > End If
> > Debug.Print Timer - t
> >
> > End Sub
> >
> > Regards,
> > Peter T
> >
> >
> > "Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote
in
> > message news:uPCo$ixhIHA.3512@TK2MSFTNGP03.phx.gbl...
> >> 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
> >>
> > <snip>
> >
> >
>

Peter
Sun Mar 16 12:19:16 CDT 2008

My turn to say ignore that - our posts crossed.

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
> Was that making a union of 2k areas (loop of 4000 cells) or 20k areas (40k
> cells). If the if the latter I can only assume your machine runs on a
> combination of nitrous-oxide and steroids. For my curiosity how long for
you
> to run this -
<snip>

Rick
Sun Mar 16 12:22:02 CDT 2008

I see you haven't read my follow up post to the one you just responded to
yet...

Rick


"Peter T" <peter_t@discussions> wrote in message
news:%23Kd5ze4hIHA.1212@TK2MSFTNGP05.phx.gbl...
> Was that making a union of 2k areas (loop of 4000 cells) or 20k areas (40k
> cells). If the if the latter I can only assume your machine runs on a
> combination of nitrous-oxide and steroids. For my curiosity how long for
> you
> to run this -
>
> Sub SteroidTest()
> Dim i As Long
> Dim t As Single
> Dim rng As Range
>
> t = Timer
>
> For i = 1 To 40000 Step 2
> If rng Is Nothing Then
> Set rng = Cells(i, 1)
> Else
> Set rng = Union(rng, Cells(i, 1))
> End If
> Next
> Debug.Print Timer - t ' ?
> Debug.Print rng.Areas.Count ' 20,000
>
> End Sub
>
> Regards,
> Peter T
>
>
> "Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
> message news:%23zaa1J4hIHA.1188@TK2MSFTNGP04.phx.gbl...
>> I got a difference of 1.18359375 seconds when I ran your code; but I have
> a
>> pretty fast computer here, so that time differential may not be
>> representative. In any case, point taken... limit the unionizing to
>> groups
>> of 100 or so... even doing that should still produce an enormous
> improvement
>> over doing it in the straight forward, one-at-a-time iteration approach.
>> Thanks for pointing that out.
>>
>> Rick
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:edwgQt1hIHA.6084@TK2MSFTNGP06.phx.gbl...
>> > One thing to watch out for with Union is that it becomes exponentially
>> > slower if/as the number of discontiguous areas in the unioned range
>> > increase. Typically it's faster to process batches of 50-400 areas at a
>> > time. The exact qty of areas depends on what the process is but a good
>> > rule
>> > of thumb is limit to about 80-100.
>> >
>> > The following should demonstrate the difference, trying to union 20k
> areas
>> > is not viable.
>> >
>> > Sub UnionDemo()
>> > Dim bFastTest As Boolean
>> > Dim bGotRng As Boolean
>> > Dim i As Long
>> > Dim arr() As Long
>> > Dim rHide As Range
>> >
>> > Rows.Hidden = False
>> > Columns(1).Clear
>> >
>> > Set rng = Range("A1:A40000")
>> >
>> > ReDim arr(1 To rng.Rows.Count, 1 To 1)
>> > For i = 1 To UBound(arr) Step 2
>> > arr(i, 1) = 1
>> > Next
>> >
>> > rng.Value = arr
>> >
>> > bFastTest = True
>> > ' bFastTest = False ' reduce rng size above to say 4k or less for
>> > testing
>> >
>> > t = Timer ' sensitive timer not required !
>> >
>> > If bFastTest Then
>> >
>> > For Each cell In rng
>> > If cell.Value = 0 Then
>> > If bGotRng Then
>> > Set rHide = Union(rHide, cell)
>> > If rHide.Areas.Count > 100 Then
>> > rHide.EntireRow.Hidden = True
>> > Set rHide = Nothing
>> > bGotRng = False
>> > End If
>> > Else
>> > Set rHide = cell
>> > bGotRng = True
>> > End If
>> > End If
>> > Next
>> >
>> > If Not rHide Is Nothing Then ' or If bGotRng ...
>> > rHide.EntireRow.Hidden = True
>> > End If
>> >
>> > Else ' do ctrl-break if it's taking to long
>> > Set rHide = Nothing
>> > For Each cell In rng
>> > If cell.Value = 0 Then
>> > If rHide Is Nothing Then
>> > Set rHide = cell
>> > Else
>> > Set rHide = Union(rHide, cell)
>> > End If
>> > End If
>> > Next
>> >
>> > If Not rHide Is Nothing Then
>> > rHide.EntireRow.Hidden = True
>> > End If
>> >
>> > End If
>> > Debug.Print Timer - t
>> >
>> > End Sub
>> >
>> > Regards,
>> > Peter T
>> >
>> >
>> > "Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote
> in
>> > message news:uPCo$ixhIHA.3512@TK2MSFTNGP03.phx.gbl...
>> >> 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
>> >>
>> > <snip>
>> >
>> >
>>
>
>

Peter
Sun Mar 16 12:27:08 CDT 2008

"Rick Rothstein (MVP - VB)" wrote in message
> Ignore what I said about my timing... I misunderstood your posting and
> thought your code measured the time-difference in a single run... I didn't
> realize it was a two-step process requiring the bFastTest be flipped
> manually for the two cases... the time I reported was the time it took to
> run the "chunk" method. I had to break into the "non-chunk" method as it
was
> taking too long.

Yes I can see now it was not clear how to test. Although normally in such a
test routine it would compare first one way then the other, but in this case
for most systems it would not be possible to do the second test which would
involve making a union of 20k areas. Hence the comment in the routine to
reduce the range before testing the 'big' union method.

Regards,
Peter T

PS more posts crossing - in future I will wait at least 5 minutes before
responding <g>

Rick
Sun Mar 16 13:43:37 CDT 2008

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" <ordnance1@comcast.net> wrote in message
> news:OOmDoUzhIHA.5780@TK2MSFTNGP06.phx.gbl...
>> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote
>> in message news:e%23s7vKzhIHA.4320@TK2MSFTNGP06.phx.gbl...
>>> Yeah, I figured it might come out just a tad faster.<g> Thanks for
>>> carrying out the experiment for me.
>>>
>>> Rick
>>>
>>>
>>> "Patrick C. Simonds" <ordnance1@comcast.net> wrote in message
>>> news:%235eqG$yhIHA.5260@TK2MSFTNGP03.phx.gbl...
>>>> 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)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote
>>>> in message news:uPCo$ixhIHA.3512@TK2MSFTNGP03.phx.gbl...
>>>>>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 <> &quo