Getting the row number of the active cell.

[wirelessengineer]

How do I get Excel to tell me the row number of the active cell? (I know there's got to be a simple VBA function.)

Now, how do I feed that into an array? I'm looping through rows testing for a blank, and when I find the upper bound and the lower bound of a group of rows (which are separated by blank rows) I want to put those into an array. I think. :wink:


Or, how do I feed the row number into a cell reference in a formula? I may go that route instead.

Any recommendations for a good VBA for Excel book? For a rookie?

 

[Joe4]

You got a lot of questions packed into this one posting. I can help you with a few.

To get the row, use "ActiveCell.Row".

For book recommendations, the Microsoft "Step-by-Step" books are good if you are know very little about VBA and need to start from scratch. I started out with that. After that, I graduated to "Excel VBA 2000 Programmer's Reference" by Wrox Press. They have a lot of good examples of little tricks and things. If you already know a little VBA, you can probably skip the "Step-by-Step" book and go right to this one.

Hope this helps some.

 

[wirelessengineer]

To get the row, use "ActiveCell.Row".

How? I don't think just putting that line of code in a macro will get me where I want to go!

Is it -SOMETHING- =ActiveCell.Row? ActiveCell.Row = SOMETHING? Somebody give me a hint, please!

 

[Juan Pablo González]

The ROW function is, I think, what you're looking for.

This

=ROW()

will return the Row number of the cell that has the formula

and this

=ROW(A1)

will return the row number of the cell A1 (1 in this case)

 

[wirelessengineer]

Gracias, Senor! That works for an Excel function, and I may go that route.


Can anyone help with the VBA code?

 

[Joe4]

For the row number VBA code, set a variable equal to the "ActiveCell.Row" statement. So if you wanted to return a pop-up telling you what row you are on, you could have the following:

Sub myActiveRow()
myRow=ActiveCell.Row
MsgBox myRow
End Sub

If you are messing with arrays or other formulas, you can use the variable "myRow" in mathematical functions and formulas (check out the Offset function in Help).

 

[wirelessengineer]

Bingo! Thanks, jmiskey!