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

Try add this at the begining:
application.screenupdating=false

and this at the end
application.screenupdating=true



On 2 Nov, 22:09, 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

Hiding rows one at a time is unbearably slow. You'd be better off
traversing the cells in rng and building a Union range of the rows
that pass your criteria for hiding. Then, hide that whole range in one
shot.

-Basilisk96

See if this is any better


Private Sub CommandButton5_Click()
Dim cell As Range, rng As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Cells.Rows.Hidden = False
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
For Each cell In rng
cell.Hidden = cell = 0
Next
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Joe2007" wrote in message
...
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

Let's say your data is A2:A10, with a header in A1.


Sub HideRows()
Dim HideRows As Range

ActiveSheet.Range("A1:A10").AutoFilter Field:=1, Criteria1:="=0",
VisibleDropDown:=False

Set HideRows = Range("A1:A10").SpecialCells(xlCellTypeVisible)
ActiveSheet.AutoFilterMode = False
HideRows.EntireRow.Hidden = True
End Sub


HTH,
JP


On Nov 2, 6:09 pm, 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

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

Sorry that should be

Sub HideRows()
Dim HideRows As Range

ActiveSheet.Range("A1:A10").AutoFilter Field:=1, Criteria1:="=0",
VisibleDropDown:=False

Set HideRows = Range("A2:A10").SpecialCells(xlCellTypeVisible)
ActiveSheet.AutoFilterMode = False
HideRows.EntireRow.Hidden = True
End Sub


Otherwise it hides the header row.

HTH,
JP



On Nov 2, 6:44 pm, JP wrote:
Let's say your data is A2:A10, with a header in A1.

Sub HideRows()
Dim HideRows As Range

ActiveSheet.Range("A1:A10").AutoFilter Field:=1, Criteria1:="=0",
VisibleDropDown:=False

Set HideRows = Range("A1:A10").SpecialCells(xlCellTypeVisible)
ActiveSheet.AutoFilterMode = False
HideRows.EntireRow.Hidden = True
End Sub

HTH,
JP

On Nov 2, 6:09 pm, 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- Hide quoted text -

- Show quoted text -

Hi, Bob. I get an error on your solution on this section

___________cell.Hidden = cell = 0______________

Private Sub CommandButton5_Click()
Dim cell As Range, rng As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Cells.Rows.Hidden = False
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
For Each cell In rng
cell.Hidden = cell = 0
Next
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

"Bob Phillips" wrote:

See if this is any better


Private Sub CommandButton5_Click()
Dim cell As Range, rng As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Cells.Rows.Hidden = False
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
For Each cell In rng
cell.Hidden = cell = 0
Next
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Joe2007" wrote in message
...
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

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

Faisal is the Winner! Will this work to speed up my other macros too?

Thanks everyone!

"Faisal..." wrote:

Try add this at the begining:
application.screenupdating=false

and this at the end
application.screenupdating=true



On 2 Nov, 22:09, 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

Faisal is the Winner! Will this work to speed up my other macros too?

Thanks everyone!

"Faisal..." wrote:

Try add this at the begining:
application.screenupdating=false

and this at the end
application.screenupdating=true


"Basilisk96" wrote:

Hiding rows one at a time is unbearably slow. You'd be better off
traversing the cells in rng and building a Union range of the rows
that pass your criteria for hiding. Then, hide that whole range in one
shot.

-Basilisk96

Faisal is the Winner! Will this work to speed up my other macros too?

Thanks everyone!

"Faisal..." wrote:

Try add this at the begining:
application.screenupdating=false

and this at the end
application.screenupdating=true


"Bob Phillips" wrote:

See if this is any better


Private Sub CommandButton5_Click()
Dim cell As Range, rng As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Cells.Rows.Hidden = False
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
For Each cell In rng
cell.Hidden = cell = 0
Next
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Joe2007" wrote in message
...
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

Faisal is the Winner! Will this work to speed up my other macros too?

Thanks everyone!

"Faisal..." wrote:

Try add this at the begining:
application.screenupdating=false

and this at the end
application.screenupdating=true


"JP" wrote:

Let's say your data is A2:A10, with a header in A1.


Sub HideRows()
Dim HideRows As Range

ActiveSheet.Range("A1:A10").AutoFilter Field:=1, Criteria1:="=0",
VisibleDropDown:=False

Set HideRows = Range("A1:A10").SpecialCells(xlCellTypeVisible)
ActiveSheet.AutoFilterMode = False
HideRows.EntireRow.Hidden = True
End Sub


HTH,
JP


On Nov 2, 6:09 pm, 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

Faisal is the Winner! Will this work to speed up my other macros too?

Thanks everyone!

"Faisal..." wrote:

Try add this at the begining:
application.screenupdating=false

and this at the end
application.screenupdating=true


"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

Hi Joel

Thanks. It depends on the type of macro. Things like flicking
between sheets or with userforms (in between these two commands) may
not work. Then ofcourse you can use more of these lines.

Faisal...


On Nov 2, 11:50 pm, Joe2007 wrote:
Faisal is the Winner! Will this work to speed up my other macros too?

Thanks everyone!



"Faisal..." wrote:
Try add this at the begining:
application.screenupdating=false

and this at the end
application.screenupdating=true
"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- Hide quoted text -

- Show quoted text -

Hi, Faisal. I have one more question. When I protect the sheet, most of it
anyway except quantities and discount, then I run this macro. I come up with
an error on the line below. Any thoughts?


___________For Each cell In rng_____________________




Private Sub CommandButton5_Click()

Application.ScreenUpdating = False

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

Application.ScreenUpdating = True

End Sub







"Faisal..." wrote:

Hi Joel

Thanks. It depends on the type of macro. Things like flicking
between sheets or with userforms (in between these two commands) may
not work. Then ofcourse you can use more of these lines.

Faisal...


On Nov 2, 11:50 pm, Joe2007 wrote:
Faisal is the Winner! Will this work to speed up my other macros too?

Thanks everyone!



"Faisal..." wrote:
Try add this at the begining:
application.screenupdating=false

and this at the end
application.screenupdating=true
"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- Hide quoted text -

- Show quoted text -

Your problem is that you named a range cell. Change it to something
else (tcell for example). Cell is a reserved word in excel VB (it is a
type just like Range, Integer, ....)

On 3 Nov, 19:00, Joe2007 wrote:
Hi, Faisal. I have one more question. When I protect the sheet, most of it
anyway except quantities and discount, then I run this macro. I come up with
an error on the line below. Any thoughts?

___________For Each cell In rng_____________________

Private Sub CommandButton5_Click()

Application.ScreenUpdating = False

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

Application.ScreenUpdating = True

End Sub



"Faisal..." wrote:
Hi Joel

Thanks. It depends on the type of macro. Things like flicking
between sheets or with userforms (in between these two commands) may
not work. Then ofcourse you can use more of these lines.

Faisal...

On Nov 2, 11:50 pm, Joe2007 wrote:
Faisal is the Winner! Will this work to speed up my other macros too?

Thanks everyone!

"Faisal..." wrote:
Try add this at the begining:
application.screenupdating=false

and this at the end
application.screenupdating=true
"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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Cell is a reserved word in excel VB (it is a
type just like Range, Integer, ....)

Surprisingly, "Cell" is not a reserved word in Excel/VBA. The plural "Cells"
is reserved, but the singular "Cell" is not.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Faisal..." wrote in message
ups.com...
Your problem is that you named a range cell. Change it to something
else (tcell for example). Cell is a reserved word in excel VB (it is a
type just like Range, Integer, ....)

On 3 Nov, 19:00, Joe2007 wrote:
Hi, Faisal. I have one more question. When I protect the sheet, most of
it
anyway except quantities and discount, then I run this macro. I come up
with
an error on the line below. Any thoughts?

___________For Each cell In rng_____________________

Private Sub CommandButton5_Click()

Application.ScreenUpdating = False

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

Application.ScreenUpdating = True

End Sub



"Faisal..." wrote:
Hi Joel

Thanks. It depends on the type of macro. Things like flicking
between sheets or with userforms (in between these two commands) may
not work. Then ofcourse you can use more of these lines.

Faisal...

On Nov 2, 11:50 pm, Joe2007 wrote:
Faisal is the Winner! Will this work to speed up my other macros
too?

Thanks everyone!

"Faisal..." wrote:
Try add this at the begining:
application.screenupdating=false

and this at the end
application.screenupdating=true
"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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

On checking again, Chip is definitely right. I apologise. Your macro
should work though.

Faisal...
On 5 Nov, 13:38, "Chip Pearson" wrote:
Cell is a reserved word in excel VB (it is a
type just like Range, Integer, ....)

Surprisingly, "Cell" is not a reserved word in Excel/VBA. The plural "Cells"
is reserved, but the singular "Cell" is not.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consultingwww.cpearson.com
(email on the web site)

"Faisal..." wrote in message

ups.com...



Your problem is that you named a range cell. Change it to something
else (tcell for example). Cell is a reserved word in excel VB (it is a
type just like Range, Integer, ....)

On 3 Nov, 19:00, Joe2007 wrote:
Hi, Faisal. I have one more question. When I protect the sheet, most of
it
anyway except quantities and discount, then I run this macro. I come up
with
an error on the line below. Any thoughts?

___________For Each cell In rng_____________________

Private Sub CommandButton5_Click()

Application.ScreenUpdating = False

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

Application.ScreenUpdating = True

End Sub

"Faisal..." wrote:
Hi Joel

Thanks. It depends on the type of macro. Things like flicking
between sheets or with userforms (in between these two commands) may
not work. Then ofcourse you can use more of these lines.

Faisal...

On Nov 2, 11:50 pm, Joe2007 wrote:
Faisal is the Winner! Will this work to speed up my other macros
too?

Thanks everyone!

"Faisal..." wrote:
Try add this at the begining:
application.screenupdating=false

and this at the end
application.screenupdating=true
"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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

You can't hide rows on a protected worksheet. Protect your sheet and
then right-click a row number or column letter, you'll see the 'Hide'
option greyed out. To allow your macro to operate on a protected
worksheet, add this code to the top of your macro:

Sheets(1).Protect Password:="Secret", UserInterFaceOnly:=True

(assuming sheet #1 is where your data is)


HTH,
JP


On Nov 3, 2:00 pm, Joe2007 wrote:
Hi, Faisal. I have one more question. When I protect the sheet, most of it
anyway except quantities and discount, then I run this macro. I come up with
an error on the line below. Any thoughts?

___________For Each cell In rng_____________________

Private Sub CommandButton5_Click()

Application.ScreenUpdating = False

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

Application.ScreenUpdating = True

End Sub

I am sorry for the confusion caused in my last two posts. Adding to
JP's comment, try this:
Private Sub CommandButton5_Click()


Application.ScreenUpdating = False

Activesheet.Unprotect(PASSWORD)

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

Activesheet.Protect Password:="Secret", UserInterFaceOnly:=True

Application.ScreenUpdating = True


End Sub



On 5 Nov, 14:48, JP wrote:
You can't hide rows on a protected worksheet. Protect your sheet and
then right-click a row number or column letter, you'll see the 'Hide'
option greyed out. To allow your macro to operate on a protected
worksheet, add this code to the top of your macro:

Sheets(1).Protect Password:="Secret", UserInterFaceOnly:=True

(assuming sheet #1 is where your data is)

HTH,
JP

On Nov 3, 2:00 pm, Joe2007 wrote:



Hi, Faisal. I have one more question. When I protect the sheet, most of it
anyway except quantities and discount, then I run this macro. I come up with
an error on the line below. Any thoughts?

___________For Each cell In rng_____________________

Private Sub CommandButton5_Click()

Application.ScreenUpdating = False

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

Application.ScreenUpdating = True

End Sub- Hide quoted text -

- Show quoted text -