Tips to efficient macros?

[Will85]

I have a macro that freezes my workbook. After running the macro, I can move around, click cells, etc etc, but once I start typing a formula, and hit enter, excel freezes. it doesnt crash, it just freezes. I can close the workbook without ctrl alt delete. When I hit enter the formula disappears, but if for example the formula was A1*B1, the ants around A1 and B1 are still visible. I can no longer click anything.

What I dont understand is, if I do the macros operations manually, excel doesnt freeze. I have about 40 worksheets in my workbook. If I remove a good chunk of worksheets and run the macro, excel doesnt freeze, which is telling to me that its some kind of maybe memory issue?

The macro simply goes through a series of worksheets and filters. Here is the code:

Sub Hide_Blank_Rows()


Application.ScreenUpdating = False


Dim ary As Variant
Dim sht As Variant

With Sheets("Macro")
ary = .Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value
End With
For Each sht In ary
With Sheets(CStr(sht))
.Unprotect "password"
.Range("A1:A350").AutoFilter 1, "1"
.Protect "password"
End With
Next sht
Sheets("Control Tab").Select

Application.ScreenUpdating = True
End Sub

 

[mole999]

Do you need to hide blank rows on 40 sheets every time, most of each sheets million rows will be blank, maybe it just needs time ti do your command ?

 

[SpillerBD]

I am suspicious of the loop that defines each sheet....