Excel Formula to find last row

Formula Solution:

The following formula will return you the row of last cell with data in the range A1:A20

=MAX(INDEX((A1:A20<>"")*ROW(A1:A20),0))

Select allOpen in new window

VBA Solution:

The following code will return you the last row with data in col. A

Sub FindLastRowWithData_1()
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox LR
End Sub

Select allOpen in new window

Sorry for not specifying but I need to be able do this using a formula, not vba, it will be used in name manager for a pivot table.  The trick is that the data will sometimes have blanks.  If row 10 is blank and row 11 had data again, I don't want it to return that 9 is the last row.

Currently I'm using this and it doesn't work correctly when there are blanks.

=OFFSET(PivotData!$A$1,0,0,MAX(2,COUNTA(PivotData!$A:$A)),COUNTA(PivotData!$1:$1))

Thank you,

Please see my previous post.

Try this.....

=OFFSET(PivotData!$A$1,0,0,MAX(INDEX((PivotData!A:A<>"")*ROW(PivotData!A:A),0)),COUNTA(PivotData!$1:$1))

Select allOpen in new window

@Scott: Please consider Requesting Attention so that you can split the points between sktneer and I since all I did was to modify his formula.