VBA EXcel get row number

[dprayner]

Hi people. I thought this would be pretty easy, but so far i've had little success. I wan to extract the row number for a cell (ie A13 = 13). I am able to get the ActiveCell.Address and I have used the right function on that, but it varies from 1 to 2 to 3 digits. I was hoping there was an easy way to determine the row of the cell that is active when a user saves a spreadsheet. Thank you DAVE

 

[dan1967]

Try ActiveCell.Row

Dan.

 

[dprayner]

I was trying to find the easiest way (function) to derive the row #. I had already found the ActiveCell.Address code which provides a user with the cell address (ie. $A$1). My challenge was to extract the 1 which can be 1 or 10 or 100 etc. which makes it hard to use the right function on the string. I used a For loop to search from the first row to the last row. When the program found the currently selected row, the For terminates and the row counter captures the current row. I find it hard to believe that MS didn't write an internal function to do this.

 

[SkipVought]

Hi,

Any RANGE (one cell or multiple cells) has, among other properties, Row, Column, Rows & Columns.

The Row property returns the first row number in the range.

So

r = Range(Z5).Row

would assign 5 to r

c = Range(Z5).Column

would assign 26 to c

rc = Range(B2:Z5).Rows.Count

would assign 4 to rc

cc = Range(B2:Z5).Columns.Count

would assign 25 to cc

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[xlbo]

um - did you not read dan1967's reply ???

the ROW is a PROPERTY of a RANGE

For each c in Range(A1:A100)
msgbox c.ROW
Next



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question