Speed Up this Macro? Part 2

[Joe2007]

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

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

Was this post helpful to you?

Why should I rate a post?

 

[OssieMac]

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

 

[Don Guillett]

Perhaps UN protecting the sheet>running the macro>protecting the sheet would
help.
Also, wouldn't filter>autofilter>filter on 0>hide visible> be a bit
faster?

 

[Joe2007]

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?

 

[Joe2007]

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.

 

[ilia]

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.

 

[Faisal...]

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