VBA: how to extract rows number-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

Try-

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