(VBA) Given 2 columns, reading across one row after the next, copy & paste into one column in another sheet.

[Opossum8117]

Hi all, first time here, pretty experienced at excel but I have a work problem that seems to need VBA to solve. Unfortunately my VBA abilities only extend to importing sheets and copy and pasting cells.

Like in the title, I have data in 2 columns, extending for a maximum of 1000 rows. Reading across one row after the other, I would like it to be copied and pasted into a single column in another sheet. Any blanks in the original data can be copied straight through.

I believe a copy is needed and not a formula reference because the data has line breaks and a simple = doesn't seem to capture that.

Here is a picture to illustrate, any help would be very much appreciated!

 

[Legacy 143009]

Hi, Welcome to the forum!

Do you want the blanks preferably or not?

 

[Fluff]

Hi & welcome to MrExcel.
If you don't want the blank cells, how about

=TOCOL(A2:B1000,1)

 

[Opossum8117]

Hi! Yes any blanks should be copied straight through preferably.

 

[Fluff]

To keep the blanks, how about

=TOCOL(FILTER(A2:B1000&"",A2:A1000<>""))

 

[Legacy 143009]

Sub test()
  Dim column1 As Variant, column2 As Variant
  column1 = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
  column2 = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row
  
  Range("D" & Cells(Rows.Count, "D").End(xlUp).Row).Offset(1).Resize(Ubound(column1, 1)).Value = column1
  Range("D" & Cells(Rows.Count, "D").End(xlUp).Row).Offset(1).Resize(Ubound(column2, 1)).Value = column2
End Sub

 

[Opossum8117]

Thank you very much for the code, I'll have a look tomorrow and let you know how it goes! Programming is a whole other art it seems. It appears tocol works as well. Just couldn't find it through my googling.