excelvba

Merge from different column until blank row


I want to merge cell value with "/" if next value of column A is blank. I attached a original picture.enter image description here

I want result like this. enter image description here

Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long, j As Long
Set ws = ThisWorkbook.Sheets("Ruff")
lastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).Row
i = 1
j = 1

For i = 3 To lastRow
     
 If Not ws.Cells(i + 1, "A").Value = "" Then
            ws.Cells(i, "G").Value = ws.Cells(i, "B").Value
 Else
    j = i
    Do Until Not IsEmpty(ws.Cells(i, 1).Value)
        ws.Cells(j, "G").Value = ws.Cells(i, "B").Value
        ws.Cells(j, "G").Value = ws.Cells(j, "G").Value & "/" & ws.Cells(i + 1, "B").Value
        i = i + 1
    Loop
    
    
 End If
Next i

End Sub   

Solution

  • Based on your description, the following should work

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim sMergedValue As String
    Set ws = ThisWorkbook.Sheets("Ruff")
    lastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).Row - 1
    i = 1
    j = 1
    
    For i = 3 To lastRow
        sMergedValue = Cells(i, "B").Value
        j = i
        Do While Cells(j, "A").Offset(1, 0).Value = ""
            '''combine values of cells while no value in column A
            sMergedValue = sMergedValue & "/" & Cells(j, "B").Offset(1, 0).Value
            j = j + 1
        Loop
        '''write the value in column C
        Cells(i, "C").Value = sMergedValue
        '''jump ahead to the next row in column A that has a value
        i = j
    Next i