CODEWITHSUNDEEP
excelvba
Frederik1111
Frederik1111

Reputation: 49

VBA Excel Mark selection but copy next column

I want to copy range from B2 to the last filled in Cell in the column. However there are breaks in the column that causes the xlDown function to stop prematurely, therefore I want to copy column A from A2 to the last filled in cell instead, since column A doesn't have any breaks in it.

Is it possible to somehow do this, and then offset the selected Range by One column, giving me the data I am after?

I am thinking something like this:

Worksheets("Sheet1").Range("A2").End (xlDown)

But how do I cause an offset so I end up with Range B2:xlDown (but the xlDown is the position of the previous selection of column A)?

Thanks,

Upvotes: 0

Views: 187

Answers (2)

Chronocidal
Chronocidal

Reputation: 7951

Don't go down from the top, go up from the bottom:

Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(2,2), Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count,2).End(xlUp)).Copy

We use Worksheets("Sheet1").Range( to define a range by 2 corners of a rectangle. Our first corner is Cell B2, a.k.a. Row 2 Column 2: Worksheets("Sheet1").Cells(2,2)

The other corner will be the bottommost cell in Column B with data in it. So, we start with the very bottom of the entire column, and move up until we hit data: 

Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count,2).End(xlUp)

Then, we copy the resulting rectangular range: ).Copy

Upvotes: 0

Mikku
Mikku

Reputation: 6654

Use this:

With Worksheets("Sheet1")

.Range("A2", .Range("A2").End(xlDown)).Offset(0, 1).Select

End With

You can use Offset Function, to shift the Column. Also Change Select to Copy or whatever else do you want.

Upvotes: 0

Related Questions