Merge cell from multiple columns into 1 cell-CodePudding

I am having an issue on how to merge cell from multiple columns into 1 cell.

Sub MergeCells()
Dim rng As Range
Dim LastRow As Long

LastRow = Cells(Rows.Count, "J").End(xlUp).Row

For Each rng In Range("J3:J" & LastRow)
rng = rng & rng.Offset(0, 1) & rng.Offset(0, 2) & rng.Offset(0, 3) & rng.Offset(0, 4) & rng.Offset(0, 5)
Next

End Sub

The issue am having is it the macro stop once reach blank cell.

I am expecting the macro to run through the excel without stopping once reach blank cell.

Thank you in advanced.

CodePudding user response：

Try the following:

Sub MergeCells()

    Dim sht As Worksheet
    Set sht = Worksheets("Sheet1") ' change this to the correct sheet name
    
    Dim LastRow As Long
    LastRow = sht.Cells(sht.Rows.Count, "J").End(xlUp).Row
    
    Dim rng As Range
    For Each rng In sht.Range("J1:J" & LastRow).Cells
        With rng
            .Value = .Value & .Offset(, 1).Value & .Offset(, 2).Value & .Offset(, 3).Value & .Offset(, 4).Value & .Offset(, 5).Value
        End With
    Next
    
End Sub