Solved Using xlDown and a defined column range

December 29, 2018 at 08:55:14
Specs: Windows 7
I have this code that is using xlDown to determine the last row of data (minus 1) but I need to go to a defined column. So the selection would be as follows:
Go to A6 then do an xlDown -1
Then select the resulting row from xlDown and combine it with column BU
as an example if the result from the xlDown is row 89 then the range to select should be A6:BU89

I tried using the xlRight but the data is inconsistent and can have blank columns in different places but I always want to go to column BU.

I can't seem to get it right, can someone help me out?

Sub AAPrepare_Pipeline_Data()
    Range("A6").Select
    Range(Selection, Selection.End(xlDown).Offset(-1)).Select
    'Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
End Sub

message edited by mecerrato


See More: Using xlDown and a defined column range

Reply ↓  Report •

#1
December 29, 2018 at 11:13:36
I received a solution from the stackoverflow forum, I am posting for others:
Sub AAPrepare_Pipeline_Data()
    Range("A6").Select
    Dim desiredRow As Integer
    desiredRow = Selection.End(xlDown).Offset(-1).Row
    Range("A6:BU" & desiredRow).Select
    Selection.Copy
End Sub


Reply ↓  Report •

#2
December 29, 2018 at 13:37:56
✔ Best Answer
I have probably mentioned this numerous times. There is rarely a need to Select an object in VBA in order to perform an operation on it.

Both of these macros will do the same thing as yours, but they will do it more efficiently. The second method (v3) is the most efficient of the two.

Note: If you are going to dimension a variable to hold a Row number, you should use Long, not Integer. An Integer variable will probably give you an Overflow error if the Range you are trying to copy exceeds ~32K Rows. Better safe than sorry.

However, if you use v3, you won't have that problem because you are not storing the Row number in a variable. One single executable instruction. The most efficient for a number of reasons.

Sub AAPrepare_Pipeline_Data_v2()
Dim desiredRow As Long

    desiredRow = Range("A6").End(xlDown).Offset(-1).Row
    Range("A6:BU" & desiredRow).Copy

End Sub


Sub AAPrepare_Pipeline_Data_v3()

    Range("A6" & ":BU" & Range("A6").End(xlDown).Offset(-1).Row).Copy

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
January 2, 2019 at 12:08:43
Wow the second solution worked nicely and much more efficiently, thanks

Reply ↓  Report •
Related Solutions


Ask Question