VBA Macro - hides columns based on a single cell which is limited by data validation

[macca_18380]

Hi All, and thank you again in advance. In a follow up to a post i made the other day about hiding rows based upon a condition, is it possible to do the same for columns? I have tried the below but struggling to get it to work. All help is much appreciated! Thank you, Michael

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$F$5" Then

Select Case Target.Value
Case "PPA Rate"
Columns("C:D").Hidden = True

Case "Dev Fee"
Rows("E:F").Hidden = True

End Select

End If

End Sub

 

[rlv01]

A couple of comments.

1. You cannot just hide things. You also need to think about how and when to unhide them.

2. As far as this goes

            Rows("E:F").Hidden = True

"E:F" is a column designation, not a row designation

You have to specify rows when you us that statement:

            Rows("3:4").Hidden = True

 

[macca_18380]

A couple of comments.

1. You cannot just hide things. You also need to think about how and when to unhide them.

2. As far as this goes

            Rows("E:F").Hidden = True

"E:F" is a column designation, not a row designation

You have to specify rows when you us that statement:

            Rows("3:4").Hidden = True

My apologies, this is my fault, i was rushing when i uploaded this question.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$5" Then

Select Case Target.Value
Case "1"
Columns("K:AC").Hidden = True
Case "2"
Columns("L:AC").Hidden = True

End Select

End If

I want to create about 20 cases where a number value goes into cell D5, so in this case if i put a value of 1 into cell D5 then i would like to hide columns K:AC - the above doesn't work, where could i be going wrong?

Thank you
Michael

 

[rlv01]

the above doesn't work, where could i be going wrong?

Two possible reasons. The first is because a subroutine needs to end with the statement End Sub. Your code is missing that statement. But if I add it, then the code works. At least to hide the columns. The second reason it might not work would be if you had put the code in a general code module instead of the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$D$5" Then
        
        Select Case Target.Value
            Case "1"
                Columns("K:AC").Hidden = True
            Case "2"
                Columns("L:AC").Hidden = True
        End Select
    End If
End Sub

BTW, for future posts , please try to use code tags like I did above when posting code. It makes your code easier to read and copy.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[macca_18380]

Thank you so much for your help, i managed to understand what i needed to do. Thanks again.​