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

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

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

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

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

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

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)" 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

<snip

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
...
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)" 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

<snip