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
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
Microsoft Excel
Last Comment
Roy Cox
r = Selection.End(xlDown).Row
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
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
then try
Sub copyrange()
Dim r As Long
r = Selection.End(xlDown).Row
Range("A1", "FF" & r).Select
End Sub
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
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
Are columns A to FF the only used columns?
If so just selecting Used.Range will do the same:
Alternatively, determine the last row using LastCell:
If so just selecting Used.Range will do the same:
ActiveSheet.UsedRange.Select
Alternatively, determine the last row using LastCell:
LR = ActiveCell.SpecialCells(xlLastCell).Row
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Cannot thank you guys enough
All suggestions were perfect in their own merit
I have one happy finance team !
All suggestions were perfect in their own merit
I have one happy finance team !
Pleased to help.
pls try ( in your code the row is the first row of the range=1)
Open in new window
Regards