Select to End on Multiple Columns

[Zakkaroo]

If I wanted to Select to the end of data in column A and then copy / paste it I could use:

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("G1").Select
ActiveSheet.Paste

But is there a way to select multiple, non-adjacent columns?

E.g

I have data going from A1 to A56 and some corresponding data in C1 to C56, F1 to F56 and H1 to H56

How do i select them ALL at the same time and then copy / paste to a new sheet?

Next week that data will be in
A1:A325
C1:C325
F1:F325
H1:H325

So I can't just define the range, it has to go to the end cell.

Any help much appreciated

 

[Phanmore]

You just need to record the final row and use that within the range.

Something like this:

Sub selectdata()
FnRow = Range("A1", Range("A1").End(xlDown)).Rows.Count
FnRange = "A1:A" & FnRow & "," & "C1:C" & FnRow & "," & "F1:F" & FnRow & "," & "H1:H" & FnRow
Range(FnRange).Copy
Sheets("YourSheetNameHere").Select
Range("A1").Select
ActiveSheet.Paste
End Sub

This code is a little bulky but does work.

 

[Desu Nota from Columbus]

Sub test()


Dim lrow As Long

lrow = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:A" & lrow).Copy Sheets("Sheet2").Range("A1:A" & lrow)
Range("C1:C" & lrow).Copy Sheets("Sheet2").Range("B1:B" & lrow)
Range("F1:F" & lrow).Copy Sheets("Sheet2").Range("C1:C" & lrow)
Range("H1:H" & lrow).Copy Sheets("Sheet2").Range("D1:D" & lrow)


End Sub

Change the "Sheet2" to the desired sheet name (the one where you want the data pasted)

 

[Zakkaroo]

Thanks for the swift replies! I have gone with Phanmores approach and it has worked fantastically! But thank you Desu for your time.