CODEWITHSUNDEEP
vbaexcel
CustomX
CustomX

Reputation: 10113

Excel VBA - Merge across

I have a tricky situation. I have a column A with only headers and column B contains text. Now I would like to get the text from column B to start in column A. If there's text in column A, B will always be empty.

A            B
Title 1
             Text 1
             Text 2
Title 2
             Text 1
             Text 2

How could I get it so the text in column B is put in column A. Range is set until a complete empty row is found. (A1 to S1 no values in the cells = empty row) I was thinking about merging cells, but that's perhaps not neat.

Upvotes: 1

Views: 3195

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Like this? This uses merging and also takes into account where A and B are both filled up.

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long

    Set ws = Sheets("Sheet5")

    With ws
        LastRow = .Range("B" & .Rows.Count).End(xlUp).Row

        For i = 1 To LastRow
            If Application.WorksheetFunction.CountA(.Range("A" & i & ":" & "B" & i)) = 1 And _
            Len(Trim(.Range("A" & i).Value)) = 0 Then
                With .Range("A" & i & ":" & "B" & i)
                    .Merge
                End With
            End If
        Next i
    End With
End Sub

FOLLOW UP

If you don't want merging and A will always remain empty when there is a value in B then we can move the value from Col B into Col A like this

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long

    Set ws = Sheets("Sheet5")

    With ws
        LastRow = .Range("B" & .Rows.Count).End(xlUp).Row

        For i = 1 To LastRow
            If Len(Trim(.Range("A" & i).Value)) = 0 Then
                .Range("A" & i).Value = .Range("B" & i).Value
                .Range("B" & i).ClearContents
            End If
        Next i
    End With
End Sub

Upvotes: 1

Related Questions