VBA Code To Perform a Merge & Center

[VinceF]

Greetings,
Thank you for any assistance.
Cell E3 contains a drop down list with #'s 2, 3 & 4 available to select.
If #2 is selected it would merge & center cells N11 & N12
If #3 is selected it would merge & center cells N11, N12 & N13
If #4 is selected it would merge & center cells N11, N12 &N14

Win10
Office 2016

VinceF

 

[Joe4]

If #4 is selected it would merge & center cells N11, N12 &N14

I am assuming that you actually meant: N11, N12, N13, & N14, because I don't understand how you would merge a disjointed range (not possible!).

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor that opens up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, cell As Range
    
'   Trigger if cell E3 is updated
    If Not (Intersect(Target, Range("E3")) Is Nothing) Then
'       First unmerge all cells in N11:N14
        Range("N11:N14").UnMerge
'       Merge appropriate cells based on selection
        Select Case Range("E3").Value
            Case 2
                Range("N11:N12").Merge
            Case 3
                Range("N11:N13").Merge
            Case 4
                Range("N11:N14").Merge
        End Select
    End If

End Sub

This should automatically do what you want, as you manually change the value in cell E3.

 

[VinceF]

I am assuming that you actually meant: N11, N12, N13, & N14, because I don't understand how you would merge a disjointed range (not possible!).

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor that opens up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, cell As Range
   
'   Trigger if cell E3 is updated
    If Not (Intersect(Target, Range("E3")) Is Nothing) Then
'       First unmerge all cells in N11:N14
        Range("N11:N14").UnMerge
'       Merge appropriate cells based on selection
        Select Case Range("E3").Value
            Case 2
                Range("N11:N12").Merge
            Case 3
                Range("N11:N13").Merge
            Case 4
                Range("N11:N14").Merge
        End Select
    End If

End Sub

This should automatically do what you want, as you manually change the value in cell E3.

Joe,

Thank you for taking the time to offer assistance...your code works fine and as hoped with the exception that it's not unmerging the cells. When there's not a number selected in cell E3 the word SELECT is in there, I suspect that has something to do with it?

VinceF

 

[Joe4]

Joe,

Thank you for taking the time to offer assistance...your code works fine and as hoped with the exception that it's not unmerging the cells. When there's not a number selected in cell E3 the word SELECT is in there, I suspect that has something to do with it?

VinceF

OK, you hadn't mentioned that requirement in your original post.

Try this update:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, cell As Range
    
'   Trigger if cell E3 is updated
    If Not (Intersect(Target, Range("E3")) Is Nothing) Then
'       First unmerge all cells in N11:N14
        Range("N11:N14").UnMerge
'       Merge appropriate cells based on selection
        Select Case Range("E3").Value
            Case 2
                Range("N11:N12").Merge
            Case 3
                Range("N11:N13").Merge
            Case 4
                Range("N11:N14").Merge
            Case Else
                Range("N11:N14").UnMerge
        End Select
    End If

End Sub

 

[VinceF]

Joe4,

Works great...thanks again for taking the time to assist, very much appreciated...!

VinceF

 

[Joe4]

You are welcome.
Glad I was able to help!