CODEWITHSUNDEEP
excelvbamergeconcatenationoffset
lambs
lambs

Reputation: 52

Excel VBA - If Column A is blank combine Column C of same row with Column C of row above

I have rows of data Some rows are blank apart from Column C If Column A is blank then I would like to concatenate Column C with column C from the row above - then delete the row. There could be situations where Column A has 2 or more blank rows, so that would require all those rows in Column C to be merged together

enter image description here

This is the code I used, but I keep getting a mismatch error - not sure where I am going wrong, but the error highlights the line with the offsets in 

Sub Merge()
Dim rng As Range
Set ws = Worksheets("test") 'Change your sheet name
Set rng = ws.Range("A1:M5600")
With ws
    For i = rng.Rows.Count To 1 Step -1
        If .Cells(i, 1) = "" Then
            .Cells(i, 3).Offset(-1) = .Cells(i, 3).Offset(-1) & .Cells(i, 3)
            .Rows(i).EntireRow.Delete
        End If
    Next
End With
End Sub

Upvotes: 0

Views: 166

Answers (1)

Dominique
Dominique

Reputation: 17491

Maybe my comment is not very clear, this is what I mean:

If .Cells(i, 1).Value = "" Then
.Cells(i, 3).Offset(-1).Value = .Cells(i, 3).Offset(-1).Value & ", " & .Cells(i, 3).Value

(I also added ", " for readability purposes)

Edit after comment

.Cells(i, 3).Offset(-1).Value = CStr(.Cells(i, 3).Offset(-1).Value) & ", " & CStr(.Cells(i, 3).Value)

Is that better?

Upvotes: 1

Related Questions