Slow Macro

[jm913]

I have a fairly simple macro that is runnning slow for some reason. Here is
the code
Application.ScreenUpdating = False
ActiveCell.Range("A1S1").Select
Selection.EntireColumn.Hidden = False
ActiveCell.Select
Application.Calculation = xlCalculationManual
For a = 3 To 200
If Range("D3").Offset(0, a).Value = "Hide Column" Then
Range("D3").Offset(0, a).EntireColumn.Hidden = True
Next a
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Any ideas?

 

[Don Guillett]

try this

Option Explicit
Sub HideIfSAS()
Dim lc As Long
Dim i As Long

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

lc = Cells(3, Columns.Count).End(xlToLeft).Column
Columns(3).Resize(lc - 3).Hidden = False
For i = lc To 3 Step -1
If Cells(3, i) = "Hide Column" Then Columns(i).Hidden = True
Next i

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

 

[Ryan H]

There are a few things you can do to speed your macro up.

1.) Declare your variables. You have a variable a that should be declared
as Dim a As Long.

2.) You can use the With Statement

3.) When looping through objects, in your case cells in a range, I would
recommend using the For Each...Next Loop instead of the For...Next Loop

Try this code. Hope this helps! If so, let me know, click "YES" below.

Sub SlowMacro()

Dim MyRange As Range
Dim rng As Range

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set MyRange = Range("A3S3")

MyRange.EntireColumn.Hidden = False

For Each rng In MyRange
If rng.Value = "Hide Column" Then
rng.EntireColumn.Hidden = True
End If
Next rng

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

 

[jm913]

I can barely tell that the macro is even running! You are the Man.

Thanks very much for your help!