Tips to efficient macros?

Will85

Board Regular
Joined
Apr 26, 2012
Messages
182
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,511
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,525
Messages
5,523,377
Members
409,514
Latest member
MarkZuckerberg

This Week's Hot Topics

Top