You've run out of memory. Instead of hiding columns one-by-one, try hiding a range of columns. Something like:
Range("F:T").EntireColumn.Hidden = True.
Excel VBA stack error 28
I have the following VBA code in an Excel spreadsheet to automatically hide columns based on a user entered value in cell B1. When I run the macro I get an error "run-time error '28': out of stack space". When I run debug it gives me the following message. What am I doing wrong? Thanks for any help you can provide!
ERROR:
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Application.Run _
"'MA_Total Rewards - Project Intake Evaluation Master_SAMPLE PROJ VERTICAL.xlsm'!Macro1"
Application.Run _
"'MA_Total Rewards - Project Intake Evaluation Master_SAMPLE PROJ VERTICAL.xlsm'!Macro1"
End Sub
CODE:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("b1").Value = "p1" Then
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("H").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
Columns("Q").EntireColumn.Hidden = True
Columns("R").EntireColumn.Hidden = True
Columns("S").EntireColumn.Hidden = True
Columns("T").EntireColumn.Hidden = True
ElseIf Range("b1").Value = "p2" Then
Columns("F").EntireColumn.Hidden = True
Columns("G").EntireColumn.Hidden = True
Columns("H").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Columns("K").EntireColumn.Hidden = False
Columns("L").EntireColumn.Hidden = False
Columns("M").EntireColumn.Hidden = False
Columns("N").EntireColumn.Hidden = False
Columns("O").EntireColumn.Hidden = False
Columns("P").EntireColumn.Hidden = True
Columns("Q").EntireColumn.Hidden = True
Columns("R").EntireColumn.Hidden = True
Columns("S").EntireColumn.Hidden = True
Columns("T").EntireColumn.Hidden = True
ElseIf Range("b1").Value = "p3" Then
Columns("F").EntireColumn.Hidden = True
Columns("G").EntireColumn.Hidden = True
Columns("H").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = False
Columns("Q").EntireColumn.Hidden = False
Columns("R").EntireColumn.Hidden = False
Columns("S").EntireColumn.Hidden = False
Columns("T").EntireColumn.Hidden = False
Else
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("H").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
Columns("K").EntireColumn.Hidden = False
Columns("L").EntireColumn.Hidden = False
Columns("M").EntireColumn.Hidden = False
Columns("N").EntireColumn.Hidden = False
Columns("O").EntireColumn.Hidden = False
Columns("P").EntireColumn.Hidden = False
Columns("Q").EntireColumn.Hidden = False
Columns("R").EntireColumn.Hidden = False
Columns("S").EntireColumn.Hidden = False
Columns("T").EntireColumn.Hidden = False
End If
End Sub
Developer technologies | Visual Basic for Applications
-
John Korchok 223.4K Reputation points Volunteer Moderator2021-12-10T03:56:42.07+00:00
1 additional answer
Sort by: Most helpful
-
Rick Lichtenfield 21 Reputation points
2021-12-11T18:04:25.907+00:00 Hi John, your suggestion worked. I have a follow up. I have protection on that worksheet and now I am getting an error. Is there a way for me to have protection on and still use the macro to protect the cells with formulas in them? Thank you again!!!
