Forum Discussion

AndreiMone's avatar
AndreiMone
Copper Contributor
Oct 17, 2023

Combine consecutive rows only if column is blank. Please advice

Hello, Could use some advice. I have to combine rows from B column if C or D column is blank, ultill the next C cell with value. In this case above, B2 sould remain only values from B2, with D 2 va...
excel
HansVogelaar's avatar
HansVogelaar
MVP
Oct 17, 2023

AndreiMone 

Here is a macro you can run:

 

Sub Condense()
    Dim r As Long
    Application.ScreenUpdating = False
    r = 2
    Do
        Do While Range("B" & r + 1).Value <> "" And _
                Range("C" & r + 1).Value = "" And _
                Range("D" & r + 1) = ""
            Range("B" & r).Value = Range("B" & r).Value & _
                " " & Range("B" & r + 1).Value
            Range("B" & r + 1).EntireRow.Delete
        Loop
        r = r + 1
    Loop Until Range("B" & r).Value = ""
    Application.ScreenUpdating = True
End Sub

 

Others will probably come up with a formula or Power Query solution.

Resources