Why range of cells aren't merging.

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,871
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
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
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
Ark68

What ranges do you want to merge?

Do you actually need to merge?

Wouldn't Center across Selection would work?
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,871
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
This works just fine for me.
Code:
      With Range("AC24:AE24, AB25:AD25,AC26:AE26,AB27:AD28")
            .MergeCells = True
            .Locked = False
        End With
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,871
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

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
Top