Returning the row below a defined range

0
votes

Is there a quick way to return the next row range outside of a defined range. My thoughts are an offset is best for this, but I'm struggling to find how the final row range can be grabbed before applying the offset.

E.g. say my range defined as Apple is A1:B100, how do I define P as my last row range:

p = Range("Apple"). [last row range function] before I use requiredrow = p.offset(1,0)

2
vbaexcel
Thanks Scott, although the p is returned as a value whereas ideally, I want p returned as a range (e.g. in my example P is returned as range("A101:B101") - which is why I thought the offset function might work! - Tan Le Nguyen
Perfect! Thank you! - Tan Le Nguyen

2 Answers

4
votes

You use this:

p = Range("Apple").Row + Range("Apple").Rows.Count

Range("Apple").Row returns the first row number and Range("Apple").Rows.Count adds the count to that.

If you want p to be set as a range that is the row directly under and the same columns then:

Dim p As Range
Dim ws As Worksheet
Set ws = Sheets(Mid(Range("Apple").Name, 2, InStr(Range("Apple").Name, "!") - 2))

With ws
    Set p = .Range(.Cells(Range("Apple").Row + Range("Apple").Rows.Count, Range("Apple").Column), .Cells(Range("Apple").Row + Range("Apple").Rows.Count, Range("Apple").Column + Range("Apple").Columns.Count - 1))
End With
Debug.Print p.Address(0, 0)
1
votes

You can still use Offset for that, together with Resize:

Dim p                     As Range
With Range("Apple")
    Set p = .Resize(1).Offset(.Rows.Count)
End With
Debug.Print p.Address(0, 0)