CODEWITHSUNDEEP
vbaexcelexcel-formulaoffice365
JayJay
JayJay

Reputation: 5

Combine specific columns (variable length) into one column - excel VBA

So the situation is that I would like to copy 4 columns with variable lengths into one column containing all the data from these 4. The problem is I loose data in the process.

As an example it should be 693 rows but I only get 648 rows all in all. I'm relatively new to VBA and have come up with these lines of code.

Sub Copy()

Dim RngA As Range, RngB As Range, RngC As Range, RngD As Range, Rng As Range
Set RngA = Range(Range("I2"), Range("I" & Rows.Count).End(xlUp))
Set RngB = Range(Range("J2"), Range("J" & Rows.Count).End(xlUp))
Set RngC = Range(Range("K2"), Range("K" & Rows.Count).End(xlUp))
Set RngD = Range(Range("L2"), Range("L" & Rows.Count).End(xlUp))
    Range("O2").Resize(RngA.Count).Value = RngA.Value
    Range("O" & RngA.Count + 1).Resize(RngB.Count).Value = RngB.Value
    Range("O" & RngB.Count + 1).Resize(RngC.Count).Value = RngC.Value
    Range("O" & RngC.Count + 1).Resize(RngD.Count).Value = RngD.Value


With Sheets("Keywords")
    Columns("O:O").Sort Key1:=.Range("=O1"), Order1:=xlAscending, Header:=xlYes
End With

End Sub

Upvotes: 0

Views: 1229

Answers (1)

SJR
SJR

Reputation: 23081

See my comment above, but here is an alternative approach using arrays which saves having to set up multiple similarly-named range variables.(Btw am assuming everything is on the Keywords sheet.)

Sub Copy()

Dim vRng(1 To 4) As Range, i As Long

With Sheets("Keywords")
    For i = LBound(vRng) To UBound(vRng)
        Set vRng(i) = .Range(.Cells(2, i + 8), .Cells(.Rows.Count, i + 8).End(xlUp))
    Next i
    For i = LBound(vRng) To UBound(vRng)
        .Range("O" & Rows.Count).End(xlUp)(2).Resize(vRng(i).Count).Value = vRng(i).Value
    Next i
    .Columns("O:O").Sort Key1:=.Range("O1"), Order1:=xlAscending, Header:=xlYes
End With

End Sub

Upvotes: 1

Related Questions