Slow Macro Issue

[WBTKbeezy]

Hello:

I have a macro that usually runs fairly quickly, but in this instance it is
running for around 45 seconds to a minute. It basically hides rows based on a
cell in a range that contains an IF statement to see if that row has a
certain name in it.

The code is this:

Sub HideVendorSummary()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.Unprotect

Dim HideCell As Range

Range("A14:A114").EntireColumn.Hidden = False
Range("A14:A114").EntireRow.Hidden = False
For Each HideCell In Range("A14:A114").Rows

If HideCell = "Y" Then
HideCell.EntireRow.Hidden = True
End If

Next HideCell

Range("A14:A114").EntireColumn.Hidden = True
Range("L:L").EntireColumn.Hidden = True
Range("G4").Select

ActiveSheet.Shapes("PMUPic").Visible = False
ActiveSheet.Shapes("GBBPic").Visible = False
ActiveSheet.Shapes("CMFPic").Visible = False
ActiveSheet.Shapes("CHD").Visible = True
ActiveSheet.Shapes("CVD").Visible = True

Range("S1").Value = "Y"
Range("F4").Select

ActiveWindow.ScrollColumn = 1

Application.Calculation = xlCalculationAutomatic
Application.Calculate
ActiveSheet.Protect

End Sub

I do not know why it is runing this slow, does anyone have any opinions. I
have read several sites about speeding up code or why code can run slow, but
as of yet have been unhelpful.

 

[Don Guillett]

I ran your code and it didn't appear slow. Try leaving Excel and coming
back. Cleaned up below
Range("a14:a114") is ROWS not COLUMNs

Sub HideVendorSummary()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.Unprotect

Dim HideCell As Range

'Range("A14:A114").EntireColumn.Hidden = False
Columns(1).Hidden = False
Rows("1:114").Hidden = False
'Range("A14:A114").EntireRow.Hidden = False
For Each HideCell In Range("A14:A114")

If UCase(HideCell) = "Y" Then
HideCell.EntireRow.Hidden = True
End If

Next HideCell
Range("a1,L1").EntireColumn.Hidden = True
'Range("A14:A114").EntireColumn.Hidden = True
'Range("L:L").EntireColumn.Hidden = True
'Range("G4").Select
With ActiveSheet
.Shapes("PMUPic").Visible = False
.Shapes("GBBPic").Visible = False
.Shapes("CMFPic").Visible = False
.Shapes("CHD").Visible = True
.Shapes("CVD").Visible = True
End With
' ActiveSheet.Shapes("PMUPic").Visible = False
' ActiveSheet.Shapes("GBBPic").Visible = False
' ActiveSheet.Shapes("CMFPic").Visible = False
' ActiveSheet.Shapes("CHD").Visible = True
' ActiveSheet.Shapes("CVD").Visible = True

Range("S1").Value = "Y"
'Range("F4").Select

'ActiveWindow.ScrollColumn = 1

Application.Calculation = xlCalculationAutomatic
Application.Calculate
ActiveSheet.Protect

End Sub