CODEWITHSUNDEEP
vbaexcel
Tan Le Nguyen
Tan Le Nguyen

Reputation: 1

Returning the row below a defined range

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)

Upvotes: 0

Views: 47

Answers (2)

Rory
Rory

Reputation: 34045

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)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

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)

Upvotes: 4

Related Questions