VBA cell row number

Thank you!!
I had .Rows with an "s", so I think that's why it wasn't working!
Another question, how could I access the first and last row of a range of cells?
Isn't there something like: Range("my_range").FirstRow???

Thank you

Re,

Maybe

Sub line_range() Dim l1, l2 Dim range As Range Set range = Range("A3:B6") With range l1 = .Row l2 = l1 + .Rows.Count - 1 End With MsgBox l1 & " and " & l2 End Sub

--
106485010510997108

Hello :)

Otherwise..

Set range = Range("A3:B6")
a = InStr(1, range.Address, ":")
line1 = Mid(range.Address, a - 1, 1)
line2 = Right(range.Address, 1)
MsgBox "line1=" & line1 & " - line2=" & line2

Thank you jjsteing, but if I named my cell range, won't it work??

Yes indeed, that's fine for me, sorry.

cool.. so post resolved ;):p

However, my cell ranges are located in a large table that can change all the time, so my ranges are "moving". They can have row numbers with 2 digits or 3 digits, so if I put:

line2 = Right(range.Address, 3), when my range number has 2 digits, it doesn't work (and I want something general that can adapt to these 2 scenarios...)

For example, if it concerns row 21, the cell in which I retrieve this number has a value of 21 € !!!! I don't know why the € sign appears!!

It is true...

So:
Set plage = Range("AA33:BA67")
CellAdd = plage.Address
a = InStr(2, CellAdd, "$")
b = InStr(1, CellAdd , ":")

ligne1 = Mid(CellAdd , a + 1, b - a - 1)

c = InStr(b + 2, CellAdd, "$")
ligne2 = Right(CellAdd , Len(CellAdd) - c)
MsgBox "ligne1=" & ligne1 & " - ligne2=" & ligne2

for your € sign check the format of your cell ;)

That works great, thank you very much!!! It will allow me to significantly lighten my code!!
I checked my cell format and I was indeed in standard format... it's strange. Anyway, it works, so that's awesome!
Thanks again :-)

Hello, another small problem...
I would like to follow the code:

Set plage = Range("AA33:BA67")
CellAdd = plage.Address
a = InStr(2, CellAdd, "$")
b = InStr(1, CellAdd , ":")

ligne1 = Mid(CellAdd , a + 1, b - a - 1)

c = InStr(b + 2, CellAdd, "$")
ligne2 = Right(CellAdd , Len(CellAdd) - c)

do:

Rows("ligne1:ligne2").Select

but it doesn't work, it gives me a type mismatch error: "type mismatch"

What should I do???

Thank you very much :-)

Hello,

Rows(line1 & ":" & line2).Select

--
106485010510997108

Re,

In fact, line1 and line2 are variables that contain a number.
Rows need the line numbers and not strings.
When you put quotes, VBA thinks that the strings line1 and line2 are numbers, that's why you get an error.
What's important is the content of line1 and line2.
--
106485010510997108

You're welcome!!!