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 -



Was this post helpful to you?

Why should I rate a post?

Hi Joe,
I realize that you directed this question to a specific person but as it has
not yet been answered I thought that you might appreciate some help from
another.

I think that your rng is not setting. That is it is nothing. Do the
specialcells conditions exist in the range being assigned to rng.?

Insert this after the line On Error GoTo 0 and it will confirm if rng is
being set:-

If Not rng Is Nothing Then
MsgBox "Range address = " & rng.Address
Else
MsgBox "Range is nothing"

End If

Regards,

OssieMac


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



Was this post helpful to you?

Why should I rate a post?

I tried this and you are right. How do I set my range? It is from B21 to
B428. I pasted the macro in question again below. Thank you.


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




"OssieMac" wrote:

Hi Joe,
I realize that you directed this question to a specific person but as it has
not yet been answered I thought that you might appreciate some help from
another.

I think that your rng is not setting. That is it is nothing. Do the
specialcells conditions exist in the range being assigned to rng.?

Insert this after the line On Error GoTo 0 and it will confirm if rng is
being set:-

If Not rng Is Nothing Then
MsgBox "Range address = " & rng.Address
Else
MsgBox "Range is nothing"

End If

Regards,

OssieMac


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



Was this post helpful to you?

Why should I rate a post?

It's the sheet protection that's causing it, and you're getting hung
up on Cells.Rows.Hide = False.

So, modify your code like this, replacing mypass with your actual
password:


Public Sub CommandButton5_Click()

Const strPass As String = "mypass"
Application.ScreenUpdating = False


Dim cell As Range, rng As Range

Me.Unprotect (strPass)
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

Me.Protect (strPass)
Application.ScreenUpdating = True


End Sub


The sheet will be unprotected only while the rows are being hidden.
Unless the user presses Ctrl+Break, the sheet will be protected again
once hiding is done. I'd also put a VBA password on the project so
that users cannot look inside the code module for the password.


On Nov 4, 3:36 pm, Joe2007 wrote:
I tried this and you are right. How do I set my range? It is from B21 to
B428. I pasted the macro in question again below. Thank you.



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
"OssieMac" wrote:
Hi Joe,
I realize that you directed this question to a specific person but as it has
not yet been answered I thought that you might appreciate some help from
another.

I think that your rng is not setting. That is it is nothing. Do the
specialcells conditions exist in the range being assigned to rng.?

Insert this after the line On Error GoTo 0 and it will confirm if rng is
being set:-

If Not rng Is Nothing Then
MsgBox "Range address = " & rng.Address
Else
MsgBox "Range is nothing"

End If

Regards,

OssieMac

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

Was this post helpful to you?

Why should I rate a post?- Hide quoted text -

- Show quoted text -

Perhaps UN protecting the sheetrunning the macroprotecting the sheet would
help.
Also, wouldn't filterautofilterfilter on 0hide visible be a bit
faster?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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



Was this post helpful to you?

Why should I rate a post?

Unprotecting the sheet first would defeat the purpose. This is a quoting
system that will be sent out to sales dept and we don't want them to have the
ability to change product master pricing or a "max" discount function. Only
want them to change quantity and discount, then hide unused product rows, and
present quote.

Any other ideas?

"Don Guillett" wrote:

Perhaps UN protecting the sheetrunning the macroprotecting the sheet would
help.
Also, wouldn't filterautofilterfilter on 0hide visible be a bit
faster?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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



Was this post helpful to you?

Why should I rate a post?

Hello

Unfortunately I did not notice this post.
The idea may be to unprotect the sheet through your macro and then at
the end you protect it again.

Ofcourse you would not want the sales guys to open your code in vb and
get the password from there. The solution will then be to password
protect your vb codes as well.

Faisal...

On 4 Nov, 20:31, Joe2007 wrote:
Unprotecting the sheet first would defeat the purpose. This is a quoting
system that will be sent out to sales dept and we don't want them to have the
ability to change product master pricing or a "max" discount function. Only
want them to change quantity and discount, then hide unused product rows, and
present quote.

Any other ideas?



"Don Guillett" wrote:
Perhaps UN protecting the sheetrunning the macroprotecting the sheet would
help.
Also, wouldn't filterautofilterfilter on 0hide visible be a bit
faster?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

Was this post helpful to you?

Why should I rate a post?- Hide quoted text -

- Show quoted text -