Unable to figure out why multiple columns are being hidden by my VBA code instead of one

[TheJay]

Hello there,

I have a dropdown box with three options and if I choose the first option, columns B:H are hidden when the code specifies column F only. Can someone please tell me where I have gone wrong and how to fix it?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = ("$C$10") Then
        If Target.Value = "No Payments" Then
            Application.Columns("F").Select
            Application.selection.EntireColumn.Hidden = True
            Range("C10").Select
        ElseIf Target.Value = "Credit on Account" Then
            Application.Columns("F").Select
            Application.selection.EntireColumn.Hidden = False
            Range("C10").Select
        ElseIf Target.Value = "Debit on Account" Then
            Application.Columns("F").Select
            Application.selection.EntireColumn.Hidden = False
            Range("C10").Select
        End If
    End If
End Sub

Thank you.

 

[jasonb75]

Merged cells spread across those columns?

edit:- I had it in mind that merged cells would cause the joined columns to be hidden, but it appears that it is not the case. Doing a quick edit to your code but I suspect that there is something else causing the issue.

 

[TheJay]

Hello Jason, yes! That's the problem. Is there another way of having titles span several columns and be centred without this happening?

 

[jasonb75]

Strange, I tried hiding a column manually with merged cells and it didn't cause any problems. See if this method works, although I would recommend not merging cells.
You can create the appearance of merged cells (horizontally) by formatting as 'Centre across selection'.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Target.Address = ("$C$10") Then
        If Target.Value = "No Payments" Then
            Range("F:F").EntireColumn.Hidden = True
        ElseIf Target.Value = "Credit on Account" Then
            Range("F:F").EntireColumn.Hidden = False
        ElseIf Target.Value = "Debit on Account" Then
            Range("F:F").EntireColumn.Hidden = False
        End If
    End If
    Application.EnableEvents = True
End Sub

 

[Joe4]

Is there another way of having titles span several columns and be centred without this happening?

Yes. Use the "Center Across Selection" formatting option instead.
See: Excel tutorial: How to use Center Across Selection in Excel

This will give you the same visual effect that Merging Across Columns does, without all the issues that Merged Cells creates.

 

[TheJay]

That's fantastic, thank you for all your help.

 

[Joe4]

You are welcome.
Glad we were able to help.

 

[TheJay]

How can I mark this as solved?

 

[Joe4]

You already did. Just reply back saying that it is solved.
Nothing more needed to do.