Selection of a column

[aner1755]

Hi All!

How can I select a column with arbitary lenght of datarange with VBA-code? Say there is a column with 10 fields in an Excelworksheet and I wish to select them all, how do I do that?

I've tried this;
Worksheets("Blad1").Activate
Worksheets("Blad1").Cells(1, 1).Select
Selection.End(xlDown).Copy
Worksheets("Blad1").Range("M1").PasteSpecial
but it gives me the last field in the column and not the whole range of the column.

Cheers!

 

[aner1755]

Ooops! Find the answer o my question;

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim myLastRow As Long
Dim myLastColumn As Long
Range("A1").Select
On Error Resume Next
myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
myLastCell = Cells(myLastRow, 1).Address
myRange = "a1:" & myLastCell
Application.ScreenUpdating = True
Range(myRange).Copy

Worksheets("Blad1").Range("M1").PasteSpecial
End Sub

This piece of code did the trick, I guess its possible achieve the same with less code, but anyhow, it works...

 

[aner1755]

A better way to do this was to;


Private Sub CommandButton2_Click()

Worksheets("Sheet1").Range("B2", Worksheets("Sheet1").Range("B2").End(xlDown)).Copy
Worksheets("Sheet2").Range("I4").PasteSpecial
Worksheets("Sheet1").Range("C2", Worksheets("Sheet1").Range("C2").End(xlDown)).Copy
Worksheets("Sheet2").Range("J4").PasteSpecial
Worksheets("Sheet1").Range("D2", Worksheets("Sheet1").Range("D2").End(xlDown)).Copy
Worksheets("Sheet2").Range("K4").PasteSpecial


End Sub