Combining VBA code for different commands

[jareed123]

Hello, I am very new to VBA so this is probably a very easy question. I am trying to hide rows and hide columns using two scripts I have found. I have tested them both on separate tabs and they work. However, when I try to combine them on the same tab, I keep having problems. I don't know if it is a problem with how I am combining the code, or if you can't hide rows and columns at the same time.

Here is the code for hide columns:

Private Sub Worksheet_Calculate()


Dim myRange As Range


For Each myRange In Range("i20:ni20")


If myRange.Value = "show" Then


myRange.EntireColumn.Hidden = False

ElseIf myRange.Value = "no" Then


myRange.EntireColumn.Hidden = True

End If

Next myRange

End Sub


Here is the code for hide rows:

Private Sub Worksheet_Calculate()


Dim myRange As Range


For Each myRange In Range("d27:d60")


If myRange.Value = "show" Then


myRange.EntireRow.Hidden = False

ElseIf myRange.Value = "no" Then


myRange.EntireRow.Hidden = True

End If


Next myRange

End Sub


I would greatly appreciate any help on how to combine them!

 

[JLGWhiz]

Try it with this modified version.

Private Sub Worksheet_Calculate()
 Dim myRange As Range
     For Each myRange In Range("i20:ni20")
         If LCase(myRange.Value) = "show" Then
             myRange.EntireColumn.Hidden = False
         ElseIf LCase(myRange.Value) = "no" Then
             myRange.EntireColumn.Hidden = True
         End If
     Next myRange
     For Each myRange In Range("d27:d60")
         If LCase(myRange.Value) = "show" Then
             myRange.EntireRow.Hidden = False
         ElseIf LCase(myRange.Value) = "no" Then
             myRange.EntireRow.Hidden = True
         End If
     Next myRange
 End Sub

 

[jareed123]

Tried it, but getting a "run time error '28' out of stack space" error. Any thoughts?

Thanks for your help!

 

[jareed123]

Re: VBA to hide rows and hide columns with formula

Changing the name of this post...

Please see original message