I'm not sure why you wrote code to differentiate between XL2007 and earlier
version as Rows.Count returns the correct value in all those versions
(mainly because number of rows in a single column fits in a Long data type).
The Count property can handle up to 2047 full columns in XL2007 before the
CountLarge property is required, so you are well short of needing to use it.
The general rule is that only if you will have 2,147,483,648 or more cells
to count will you ever need to use CountLarge. With that said, you can
replace all of the code in your function with this...

With whatSheet
FindLastRow = .Cells(.Rows.Count, whichCol).End(xlUp).Row + _
(WorksheetFunction.CountA(.Columns(whichCol)) = 0)
End With

Using this code, your function will return 0 if there is no data in the
specified column and the actual row number for the last data in the column
if there is data there. I should also point out that, for both your original
code and my modification for it, if you have formulas in the column which
evaluate to the empty string (""), then those will be treated as data.

--
Rick (MVP - Excel)


"Mike" wrote in message
...
try this.

Sub lastRow()
MsgBox FindLastRow(Worksheets("Sheet1"), "A")
End Sub

Private Function FindLastRow(whatSheet As Worksheet, whichCol As String)
As
Long
'this finds and returns the actual last row on a sheet
'that has entry in specified column
'NOT the next row available for data entry
'so calling routine should add 1 to the returned value
'to determine next row available for new entry
'when it is found that a sheet has no entries, this
'routine will (properly) return zero.

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
FindLastRow = whatSheet.Range(whichCol & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 or later
FindLastRow = whatSheet.Range(whichCol &
Rows.CountLarge).End(xlUp).Row
End If
If FindLastRow = 1 And IsEmpty(whatSheet.Range(whichCol & "1")) Then
FindLastRow = 0 ' no entries at all in the column on the sheet
End If

End Function

"jonnybrovo815" wrote:

I run this bit of code to find the address of the the last cell in column
K.

Sub test()
Dim x As Range
Worksheets("NCSA_ISS_ITEM_BOM").Activate
Set x = Cells(Rows.Count, "K").End(xlDown)
MsgBox x.Address
End Sub

I would like to save the resulting row address as a variable to run as
part
of another set of code.

How would I go about doing this?