Merge Cells in Range

[billandrew]

Looking to merge 2 cells in Range if there is data.

i.e.
Columns F:G

Columns I:J

 

[nitishshr]

Does both the cell contain data? Column F and G

 

[billandrew]

Columns F abd Columns G contain the data

 

[billandrew]

This is the code I am using.
Dim rg as Range
Lr = cells(Rows.count,6).End(xlUp).Row
For i = 9 to Lr

Set rg = Range(Cells(i,6), Cells(I + 1, 7))

With rng

.HorizontalAlignment = True
.VerticalAlignment = True
.WrapText = True

End With

Next i

 

[Joe4]

I would recommend using "Center Across Selection" instead of merging the cells. Merged cells are a HUGE source of headaches in Excel, and should really be avoided, whenever possible.
See: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

 

[billandrew]

Thank You

added the suggestion

My code looks like this below, Can this be cleaned up possibly?

Sub mergeColumns()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range

lr = Cells(Rows.Count, 6).End(xlUp).Row

For i = 9 To lr

Set rng1 = Range(Cells(i, 6), Cells(i + 1, 7))

With rng1

.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.WrapText = True

End With

Next i

For i = 9 To lr

Set rng2 = Range(Cells(i, 9), Cells(i + 1, 10))

With rng2

.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.WrapText = True

End With

Next

For i = 9 To lr

Set rng2 = Range(Cells(i, 9), Cells(i + 1, 10))

With rng2

.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.WrapText = True

End With

Next



For i = 9 To lr

Set rng3 = Range(Cells(i, 11), Cells(i + 1, 13))

With rng3

.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.WrapText = True

End With

Next


Cells(1, 1).Select

End Sub

 

[Joe4]

Try this:

Sub mergeColumns()

    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range

    Application.ScreenUpdating = False

    lr = Cells(Rows.Count, 6).End(xlUp).Row

    For i = 9 To lr
        
        Set rng1 = Range(Cells(i, 6), Cells(i + 1, 7))
        With rng1
            .HorizontalAlignment = xlCenterAcrossSelection
            .VerticalAlignment = xlCenter
            .WrapText = True
        End With

        Set rng2 = Range(Cells(i, 9), Cells(i + 1, 10))
        With rng2
            .HorizontalAlignment = xlCenterAcrossSelection
            .VerticalAlignment = xlCenter
            .WrapText = True
        End With
    
        Set rng3 = Range(Cells(i, 11), Cells(i + 1, 13))
        With rng3
            .HorizontalAlignment = xlCenterAcrossSelection
            .VerticalAlignment = xlCenter
            .WrapText = True
        End With

    Next i

    Application.ScreenUpdating = True

    Cells(1, 1).Select

End Sub

 

[billandrew]

Thank You sir.

 

[Joe4]

You are welcome.