CODEWITHSUNDEEP
excelvba
MEC
MEC

Reputation: 233

Using XlDown and select a defined column range

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

Upvotes: 0

Views: 3036

Answers (2)

41686d6564
41686d6564

Reputation: 19641

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

Upvotes: -1

user10829321
user10829321

Reputation:

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.

Upvotes: 1

Related Questions