Speed Up this macro?

[JP]

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

 

[Faisal...]

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

 

[Daniel Gash-Jones]

Actually you can hide rows on a protected worksheet by using the following code when you protect your sheet:

sheets(1).Protect Password:="password", AllowFormattingRows:=True

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