VBA cell row number Solved

Question

Hello,
I would like to know if it's possible to retrieve the row number of a cell? That is to say, if I have the cell A3, I want to retrieve 3. How should I proceed (in VBA)?

Thank you very much for your help

Configuration: Windows XP Internet Explorer 7.0

lami20j · Accepted Answer

Hello,  Sub Line() MsgBox Range("A3").Row End Sub  -- 106485010510997108

ori05 · Answer

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

lami20j · Answer

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

jjsteing · Answer

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

ori05 · Answer

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

ori05 · Answer

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

jjsteing · Answer

cool.. so post resolved ;):p

ori05 · Answer

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!!

jjsteing · Answer

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 ;)

ori05 · Answer

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 :-)

ori05 · Answer

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 :-)

lami20j · Answer

Hello,  Rows(line1 & ":" & line2).Select -- 106485010510997108

lami20j · Answer

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

ori05 · Answer

You're welcome!!!

VBA cell row number

14 réponses

Access issue to my "contact me" profile

Windows spotlight is not working.

Samsung j5 unable to answer

My nikon coolpix s3000 won't turn on anymore.

Bug with the "backspace" key

Do you know maillotsofficiels.fr?

Wii balance board issues

Focusing issue on canon eos 450d

Why do i need to press f1 to start

Wi-fi connection via livebox