Merge cell from multiple columns into 1 cell

Question

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.

Yes, I do know but I'm just wanted to built a macro so everything can be automated. — TK4795, Jan 13 '23 at 09:30
Put a breakpoint on the line `For Each rng In Range("J3:J" & LastRow)` and check what is the value of `LastRow`. Then manually check the worksheet. Are the the same? — Siddharth Rout, Jan 13 '23 at 09:33
Perfect thank you, `For Each rng In Range("J3:J" & LastRow)` I have changed to `For Each rng In Range("J3:J1000" & LastRow)` and it worked. — TK4795, Jan 13 '23 at 09:41
Your 'solution' will cause an error if LastRow is higher than 999.. Please follow SiddharthRout's comment to examine why it's stopping before you're expecting it to. — CLR, Jan 13 '23 at 10:52
If `LastRow` is 1000 your solution would try and go to cell `J10001000` and give you an error instead. — Darren Bartrup-Cook, Jan 13 '23 at 11:00
Thank you guys for your help, I have follow @SiddharthRout solution though J3:J1000 is absolutely fine. Everything is working perfectly. — TK4795, Jan 14 '23 at 05:30
No. You have misunderstood what I was suggesting. Please re-read what I was asking you to do. **1.** Did you try what @CLR posted below? Ensure you have set the correct worksheet name at `Set sht = Worksheets("Sheet1")` **2.** Ensure that you are using the correct column to find the last row. For example, If Column `A` has more rows then `J` then use `A` instead of `J`. Or better, use [.Find](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba/11169920#11169920) to find the last row in the worksheet. — Siddharth Rout, Jan 14 '23 at 05:47

score 2 · Accepted Answer · answered Jan 13 '23 at 15:19

2

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

answered Jan 13 '23 at 15:19

CLR

11,284
1
11
29

Thank you for you help, I have now successfully have a working macro. – TK4795 Jan 14 '23 at 05:31
I would recommend trying this code @TK4795. If it still doesnt work then find the correct last row as mentioned in my comment below your question. – Siddharth Rout Jan 14 '23 at 06:07

Merge cell from multiple columns into 1 cell

1 Answers1