Excel 2007 is slow when you hide columns on .xls

D

Diego Anaya

Hi everybody, I have a macro that hide a few columns on excel model 2003, but
acttually i'm using excel 2007 and is too slow, to hide this columns takes 1
minutes, here is the code.

Application.ScreenUpdating = False
Application.Calculation = xlManual

With ActiveSheet
For i = 1 To 11
Worksheets(aNombreHojas).Columns(5 + i).Hidden = true
Next
End With

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
 
M

Martin Brown

Diego said:
Hi everybody, I have a macro that hide a few columns on excel model 2003, but
acttually i'm using excel 2007 and is too slow, to hide this columns takes 1
minutes, here is the code.

Application.ScreenUpdating = False
Application.Calculation = xlManual

With ActiveSheet
For i = 1 To 11
Worksheets(aNombreHojas).Columns(5 + i).Hidden = true
Next
End With

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

Runs instantly here. Are there graphs/charts/shapes in the hidden
columns or on the sheet with the columns being hidden?

Or other OnAction events being triggered .

Worksheets(1).Columns("F:p").Hidden = True

Might be faster if the problem is redraw related. I have noticed that in
some complex circustances ScreenUpdating = False *slows* down XL2007.

Regards,
Martin Brown
 
D

Dave Peterson

I didn't test this, but since you're hiding 11 contiguous columns, maybe just
doing them all at once would help:

Worksheets(aNombreHojas).cells(1,6).resize(1,11).entirecolumn.Hidden = true

ps.

You can drop that outer "with Activesheet/End with" structure. You're not
doing anything against the activesheet. (Well, not in the code you shared...)
 
E

excelent

With ActiveSheet
Columns("F:p").EntireColumn.Hidden = True
End With


"Diego Anaya" skrev:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top