Select range using cell reference (VBA)

[ianto2842]

I am trying to get VBA to select a range of cells to copy based on a value in a given cell. The range always starts in the same place, same columns, but down to a particular row depending on the data. I cant use xlup as it isn't the last row and there are intentionally blank rows plus rows that appear blank but have formulae in them - so aren't seen as blank.

I need it to be able to select the area C3:J(value in B3) of the current sheet. Any help would be greatly appreciated! Using 2013.

 

[mart37]

Is the value in B3 unique?
Then you can use MATCH to find the row.

 

[Norie]

Try this.

Dim rng As Range
Dim LastRow As Long

    ' get last row from B3
    LastRow = Range("B3").Value

    ' set rng to refer to required range
    Set rng = Range("C3:J" & LastRow)

    ' do stuff with rng
    rng.Select

    rng.Copy

 

[ianto2842]

Try this.

Dim rng As Range
Dim LastRow As Long

    ' get last row from B3
    LastRow = Range("B3").Value

    ' set rng to refer to required range
    Set rng = Range("C3:J" & LastRow)

    ' do stuff with rng
    rng.Select

    rng.Copy

That's brilliant, does exactly what I need! Thank you :D