VBA to select range

[betgem]

Hello, everyone !

I need a macro that will select and copy a range within a worksheet. Now the range always starts in cell A22 and ends in column J, but in a different row, so could be A22:J40, A22:J67 etc. In a separate cell I have a formula that returns a value that I want to use as the cell reference for my second part of the range (if the cell value is 40, then my range will be A22:J40, if my cell value is 67, then my range should be A22:J67 etc.)
Or to paraphrase the question - how to select a range of cells using cell values as references for the range.

I hope this makes sense and thank you all for your time.

 

[Andrew Poulsom]

If the row number is in A1 on Sheet1:

Range("A22:J" & Worksheets("Sheet1").Range("A1").Value).Select

 

[Sixthsense]

Try this...

In the below code A1 cell will determine the end cell of column-J

Sub SelectDataBasedOnEndRangeMentionedInCell()
    Range("A22:J" & Range("A1").Value).Select
End Sub

 

[betgem]

Thank you both Andrew and Sixthsense, this worked well and was exactly what I needed. Thank you for your time.