asked on

VBA Selecting range only containing data

HI Guys

Im using this bit of code which I thought would select Column A to FF and go to the last row containing data in this range.

Sub selectrange()
Dim r As Long
r = Range(Selection, Selection.End(xlDown)).Row
Range("A" & Selection.Row, "FF" & r).Select
End Sub

It is only selecting the first row for some reason and Im not sure what the issue is.

Would you be able to help me on this?

The attached example should result in the range A1:FF31 being selected when the code is run but unfortunately, im missing something ..

J
Book1.xlsm

Rgonzo1971

Hi,

pls try ( in your code the row is the first row of the range=1)

Sub copyrange()
Dim r As Long
r = Selection.End(xlDown).Row
Range("A" & Selection.Row, "FF" & r).Select
End Sub

Open in new window

Regards

Saqib Husain

r = Selection.End(xlDown).Row

Jase Alexander

ASKER

HI Guys

Thank you for the quick response

It work great except is there an addition to the code whereby it ALWAYS selects from cell A1 and the subsequent range containing data?

At the moment, if I click in cell d8 and run the code, it only selects the rows and range starting below this location?

If not, no problems I can work with this

Regards
J

Rgonzo1971

then try

Sub copyrange()
Dim r As Long
r = Selection.End(xlDown).Row
Range("A1", "FF" & r).Select
End Sub

Open in new window

Jase Alexander

ASKER

HI RG

I tried the A1 change but what happens is if you click in A1 and then run the code, it works perfect.

If you click anywhere else on the sheet and run the code, it selects the WHOLE sheet from cell A1

However, no problems, I can work with this

Thank you for your help

J

Rob Henson

Are columns A to FF the only used columns?

If so just selecting Used.Range will do the same:

ActiveSheet.UsedRange.Select

Open in new window

Alternatively, determine the last row using LastCell:

LR = ActiveCell.SpecialCells(xlLastCell).Row

Open in new window

Rob Henson

What are you doing with the range once it is selected?

It is often not necessary to use the .Select method with a range; you may be able to do the actions without selecting the range.

Thanks
Rob H

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION