Merge cells

[bobaol]

Hi, I have a question about merging cells in a row. Please consider the grid below, in a jpg image. So, in this situation, I want to merge A1 to K1, merge C2 and D2, merge H2 and I2, merge C3 to G3, merge H3 to K3. The blue cell, "Merge1Begin" is the start of the beginning a merged cell. For example, I want to merge c3 to g3 because it begins with Merge1Begin, then it has Merge2. So, I want C3 to G3 merged because of this pattern. I want to merge cells based on this pattern. any help is appreciated. thanks in advance.

 

[6StringJazzer]

You simply select each set of cells you want to merge, then click the Merge button. That's very straightforward, so maybe I'm not clear on what your question is.

Also, merging cells is almost always a bad practice that causes problems. You should avoid it.

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Merge1Begin
2			Merge1Begin					Merge1Begin
3			Merge1Begin					Merge1Begin
Merging

 

[bobaol]

Hello, thanks for the response. I'm looking for VBA to do this and automate it. I have 300 tables, and they are all different. yes, i dislike merged cells. this is part of a presentation to make it look pretty.

 

[Joe4]

Note that all your example seem to be merging cells across single rows. In that case, it would be better to use the "Center Across Selection" formatting option instead.
That gives you the same visual effect as merged cells, but without all the issues that merged cells causes.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

By the way, do your cells really have the words "Merge1Begin" and "Merge2" in them?
What do you want it to say in them after they have been combined?

 

[Joe4]

Here is some VBA code that will apply the "Center Across Selection" across those cells you want to combine.
It will keep the "Merge1Begin" text and remove the the "Merge2" text.

Sub MyFormatMacro()

    Dim lr As Long, r As Long
    Dim lc As Long, c As Long
    Dim c1 As Long, c2 As Long
    Dim ms As Boolean
    
    Application.ScreenUpdating = False
    
'   Find last row in on sheet with data
    lr = Range("A1").SpecialCells(xlLastCell).Row
    
'   Loop through all rows
    For r = 1 To lr
'       Find last cell in column with data
        lc = Cells(r, Columns.Count).End(xlToLeft).Column
'       Initialize merge section boolean variable
        ms = False
'       Loop through all columns
        For c = 1 To (lc + 1)
'
            Select Case Cells(r, c)
                Case "Merge1Begin"
'                   See if previous merge section
                    If ms Then
'                       Merge section
                        Range(Cells(r, c1), Cells(r, c2)).HorizontalAlignment = xlCenterAcrossSelection
                    End If
'                   Mark start of merge section
                    ms = True
                    c1 = c
                Case "Merge2"
'                   Increment merge section
                    c2 = c
'                   Clear out word "Merge2"
                    Cells(r, c).ClearContents
                Case Else
'                   Check to see if end of merge section
                    If ms Then
'                       Merge section
                        Range(Cells(r, c1), Cells(r, c2)).HorizontalAlignment = xlCenterAcrossSelection
'                       Reset merge section variable
                        ms = False
                    End If
            End Select
        Next c
    Next r
        
    Application.ScreenUpdating = False
    
    MsgBox "Macro Complete!"
    
End Sub

Not the most elegant code, but gets the job done, I believe.

 

[bobaol]

Thanks for the response. the code did not work. the code deleted all the Merge2, kept Merge1Begin, but the cells did not merge. thanks.

 

[bebo021999]

Try this code:

Loading Google Sheets

Option Explicit
Sub mergeCell()
Dim ce As Range, ce1 As Range
For Each ce In ActiveSheet.UsedRange
    If ce.MergeCells = False And ce.Value = "Merge1Begin" Then
        Set ce1 = ce.Offset(, 1)
        Do
            If ce1.Value = "Merge2" Then
                Set ce1 = ce1.Offset(, 1)
            Else
                Set ce1 = ce1.Offset(, -1)
                Exit Do
            End If
        Loop
        Application.DisplayAlerts = False
        With Range(ce, ce1)
            .MergeCells = True
            .HorizontalAlignment = xlCenter
        End With
        Application.DisplayAlerts = True
    End If
Next
End Sub

 

[6StringJazzer]

The cells weren't supposed to merge. The code uses Center Across Selection so they give the same appearance as merged cells without actually merging them.

 

[Joe4]

Thanks for the response. the code did not work. the code deleted all the Merge2, kept Merge1Begin, but the cells did not merge. thanks.

As Jeff said, they were not supposed to. They were supposed to use the "Center Across Selection" formatting option instead, which gives you the same visual effect as merged cells, but without all the all problems merged cells cause (merged cells are a horrible, horrible thing, and she be avoided whenever possible).

If you read the link I provided, you will understand better.

 

[bobaol]

thanks everyone for the response. bebo's code worked for my needs. As for merging, i mostly agree with the comments. I fully agree when analyzing data or manipulating data. When i get a file, one of the first things i do is uncheck Merge Cells, and uncheck Wrap in the entire sheet. That being said, there is one time when merging is required, such as presenting the data in a view that makes sense to the audience. Usually, this is about formatting a table, then screen-shot it for pasting to PowerPoint. Formatting the data for a presentation is the single only time i use merged cells. i cannot think of any other instance where i would use merged cells. again, thanks everyone!