Jim, yours hides the rows lightening fast, but the sheet cycles through the
other command buttons and makes them flash. Almost like we got to the end
result on the sheet really fast, but the blinking command buttons still goes
on for just as long as it did before your fix. We have half of the problem
solved in the speed of the hiding rows situation, but the blinking command
buttons (which ties up the sheet) still takes just as long as before. I
really appreciate the assistance. Any Ideas?


"Jim Thomlinson" wrote:

In XL 2003 the act of hiding and unhiding triggers a calculation (different
totaling for hidden and unhidden type stuff). So every time you hide a row
you trigger a calc. Give this a try. It uses the suggestion posted by
Basilisk96 of createing a single big range to be hidden all at once...

Private Sub CommandButton5_Click()

Dim cell As Range, rng As Range
Dim rngToHide as range

Cells.Rows.Hidden = False
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
For Each cell In rng
If cell.Value = 0 Then
if rngToHide is nothing then
set rngtohide = rng
else
set rngToHide = union(rng, rngToHide)
end if
End If
if not rngtohide is nothing then rngtohide.entirerow.Hidden = True
Next
End Sub

That sub could be made faster still by using the Find method but even this
should make a big difference. If you wnat help with using Find then reply
back...
--
HTH...

Jim Thomlinson


"Joe2007" wrote:

I am using Excel as a quoting system. This Macro hides rows that do not have
quantities in them, but they have to have a zero value in them, not just
blank. It worked pretty fast until I added about a dozen simple macros to
simply hide rows and columns, those macros were not dependent on any data,
simply just highlighted the rows and columns and hid them.

After adding these "simple" macros, the original macro to hide un-used
products with no quantities went EXTREMELY slow on me. All of the command
buttons, I assigned the "simple" macros to blink when I run this most
important macro. The code is below:

Private Sub CommandButton5_Click()

Dim cell As Range, rng As Range
Cells.Rows.Hidden = False
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
For Each cell In rng
If cell.Value = 0 Then
cell.EntireRow.Hidden = True
End If
Next
End Sub