Excel 2007 is slow when you hide columns on .xls

[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

 

[Martin Brown]

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

 

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

 

[excelent]

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


"Diego Anaya" skrev: