CODEWITHSUNDEEP
excelvba
Luca Guglielmini
Luca Guglielmini

Reputation: 21

VBA: how to extract rows number

I have a cell in excel named "Start_Database" in A9.

I want to extract the number 9 in VBA, but I don't know how.

What I do now is writing y = 9, but I would like to know an alternative way to the same task, without writing "9".

Can anyone solve my problem?

Upvotes: 1

Views: 65

Answers (3)

Алексей Р
Алексей Р

Reputation: 7627

Debug.Print ActiveWorkbook.Names("Start_Database").RefersToRange(1).Row ' 9

Upvotes: 3

Vasily
Vasily

Reputation: 5782

you can use something like this for named range Start_Database:

Sub foo()
    Debug.Pring Split([Start_Database].address(),"$")(2) 'or you can use more native way Range("Start_Database") instead of [Start_Database]
End Sub

or just for range:

Sub foo()
    Debug.Pring Split([A9].address(),"$")(2) ' or Range("A9") instead of [A9]
End Sub

or more simple way:

Sub foo()
    Debug.Pring [Start_Database].row
    Debug.Pring [A9].row
End Sub

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 37125

Try-

Sub ExtractColNum()
Dim arr As Variant
    arr = Split(Range("B9").Address, "$")
    Debug.Print arr(UBound(arr))
End Sub

Upvotes: 0

Related Questions