Finding starting row number of a range name of Excel

Question

Hi,

In an Excel worksheet, I have a range name "LOOPBACK_IP". I want to programmatically find the starting row of this range name.

With Powershell, if I want to find the value of this range, I use (after assigning $ws variable a worksheet):

write-host $ws.Range("LOOPBACK_IP").Value2

But to find the location (i.e. row, column) of a range name, what do I do?

Thanks.

Answer 1

The first row is the first element of $ws.Range("LOOPBACK_IP").Rows

... the property for the row number is .Row

For example in VBA, the following shows a msgbox with the number of each row

For Each rw In MyRange.Rows
    MsgBox rw.Row 
Next rw

Answer 2

Not sure if you can call this the same way from Powershell, but the ROW function called on a range will return the starting row of the range. If you had a range RangeName that started at cell C3, =ROW(RangeName) would return 3.

I think this may only work properly if your range is rectangular, but I can't confirm at the moment.

Answer 3

Names("LOOPBACK_IP").RefersToRange.Row gives you the row; .Column gives you the (numeric) column.

You can also use Names("LOOPBACK_IP").RefersTo or Names("LOOPBACK_IP").RefersToLocal would give something like =Sheet1!$D$9:$D$30, if that might be useful - though I must admit I don't know what makes 'Local' any different.