Why range of cells aren't merging.

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have this code:

Code:
        If Application.VLookup(FacB, wshfac.Range("A:H"), 6, False) = "N" Then
            With .Range("AB24:AE27")
                .Font.Color = RGB(224, 224, 224)
                '.Locked = True
            End With
        End If
        With .Range("AB24:AB24, AC24:AE24, AB25:AD25, AB26:AB26, AC26:AE26, AB27:AD27, AA27:AD27, AE27:AE27")
            .MergeCells = True
            .Locked = True
        End With

If the value in column 6 of wshfac.range("A:H") of the looked up value FacB = "N", which in my sample ase it does ...
The code in blue will change the font colour of the contents in range AB24:AE27 to grey, and the code in red will merge a series of cell ranges.

The merging of range AC24:AE24 and AC26:AE26 is not working. Everything else does. The sheet is not protected.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You can't merge multiple, non-contiguous ranges at the same time.

Try this.
Code:
Dim ar As Range

    For Each ar In .Range("AB24:AB24, AC24:AE24, AB25:AD25, AB26:AB26, AC26:AE26, AB27:AD27, AA27:AD27, AE27:AE27").Areas

        ar.MergeCells = True
        ar.Locked = True
    Next
 
Upvote 0
You can't merge multiple, non-contiguous ranges at the same time.

Sure you can, you just have to do it differently.

Code:
Sub test()
With Union(.Range("AB24:AB24"), .Range("AC24:AE24"), .Range("AB25:AD25"), .Range("AB26:AB26"), _
    .Range("AC26:AE26"), .Range("AB27:AD27"), .Range("AA27:AD27"), .Range("AE27:AE27"))
    .MergeCells = True
    .Locked = True
End With
End Sub
 
Upvote 0
Good point. :confused:
Actually, I just deleted my message (which is the one I think you were referring to in your reple). Why? Because when I ran your code, I realized he wasn't really merging single cells... he was actually merging contiguous cells but he specified them in an odd way.
 
Upvote 0
Ark68

What ranges do you want to merge?

Do you actually need to merge?

Wouldn't Center across Selection would work?
 
Upvote 0
I think I may have been misleading.

I'd like to merge cells {AC24, AD24, AE24}, {AB25, AC25, AD25}, {AC26, AD26, AE26}, {AB27, AC27, AD28}.

I have used this similar routine for many other cell ranges with no problems. This is the only scenario it's not.
eg, this similar code works in another module:
Code:
        With Range("AI24:AL24, AJ25:AL25, AI26:AK26")
            .MergeCells = True
            .Locked = False
        End With


I use these individual merged cell ranges as fields for drop down list, I'd prefer to merge.
 
Upvote 0
This works just fine for me.
Code:
      With Range("AC24:AE24, AB25:AD25,AC26:AE26,AB27:AD28")
            .MergeCells = True
            .Locked = False
        End With
 
Upvote 0
I think I may have been misleading.

I'd like to merge cells {AC24, AD24, AE24}, {AB25, AC25, AD25}, {AC26, AD26, AE26}, {AB27, AC27, AD28}.

I have used this similar routine for many other cell ranges with no problems. This is the only scenario it's not.
eg, this similar code works in another module:
Code:
        With Range("AI24:AL24, AJ25:AL25, AI26:AK26")
            .MergeCells = True
            .Locked = False
        End With


I use these individual merged cell ranges as fields for drop down list, I'd prefer to merge.
Try it like this...
Code:
Sub MergeMyCells()
  Dim Cell As Range
  For Each Cell In Range("AC24,AB25,AC26,AB27")
    With Cell.Resize(, 3)
      .MergeCells = True
      .Locked = False
    End With
  Next
End Sub
Note that single cells do not have to be specified using "Address:Address" format.
 
Upvote 0
Thanks folks ...

Norie, as I said, I have no idea why it didn't work in my situation as similar have worked consistently elsewhere. Needless to say, Rick, your suggestion solved the problem. Thanks!
 
Upvote 0

Forum statistics

Threads
1,218,596
Messages
6,143,393
Members
450,484
Latest member
ChrisMac1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top