Using XlDown and select a defined column range

Question

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

user10829321 · Accepted Answer · 2018-12-29 18:03:00Z

Use the same code to gain the cells in column A but Resize the columns to 73 columns wide before the Select or Copy command.

Sub AAPrepare_Pipeline_Data() Range(Range("A6"), Range("A6").End(xlDown).Offset(-1)).Resize(, 73).Copy End Sub

You don't need to Select something in order to reference or copy it.

41686d6564 · Accepted Answer · 2018-12-29 18:08:36Z

You can get the row number you desire, use it to create the range, and then select that range. Something like this should work:

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

Collectives™ on Stack Overflow

Using XlDown and select a defined column range

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related