Get Row Number

  • Thread starter Thread starter BigFoot
  • Start date Start date
Previous post from Don Pistulka:-

Formula (Array - trl-Shift-Enter

=ADDRESS(ROW(OFFSET( $A$1,COUNTA($A$1:$A$65000),0)),COLUMN(OFFSET(
$A$1,COUNTA( $A$1:$A$65000),0)))

or

Macro:

Sub NextBlankDown()
Range("a1").Activate
If ActiveCell.End(xlDown).Row = Rows.Count Then Exit Sub
MsgBox (ActiveCell.End(xlDown).Offset(1, 0).Address)
End Sub
 
Let A10 be the last cell in column A that house something...

Are you interested in the row number of an empty cell within in A1:A10 or
that of A11?
 
Ken said:
Previous post from Don Pistulka:-

Formula (Array - trl-Shift-Enter

=ADDRESS(ROW(OFFSET( $A$1,COUNTA($A$1:$A$65000),0)),COLUMN(OFFSET(
$A$1,COUNTA( $A$1:$A$65000),0)))
Click to expand...

That seems to return unwanted results
A1 56
A2 96
A3
A4 78
A5 99
A6
A7
A8 123

array formula returns A6 ,the first blankcell is A3 ..
or

Macro:

Sub NextBlankDown()
Range("a1").Activate
If ActiveCell.End(xlDown).Row = Rows.Count Then Exit Sub
MsgBox (ActiveCell.End(xlDown).Offset(1, 0).Address)
End Sub
Click to expand...

that works nicely
 
you can do that with Counta
=counta(A:A)
I'm interested in something similar to SQL Server
RowCount. I want to obtain the number of rows that
contain data (not all the rows in the spreadsheet i.e.
Rows.Count). If I know the row number of A11 in your
example, I can determine the number of rows that contain
data.

Thanks again
Click to expand...
 
If there are no empty cells between A1 and the last cell used in column A,

=COUNTA(A:A)+1

will suffice.
 
Back
Top