Sub Macro1()
'
' Macro to find the last Row No of a typed column  cells.
' Macro Created on 1/24/2006 by Mactikus
' Assign shortcut key Ctrl+Shift+S
RowNo = Cells.SpecialCells(xlCellTypeLastCell).Row
ColNo = Cells.SpecialCells(xlCellTypeLastCell).Column
For i = RowNo To 1 Step -1
Cells(1, 5).Formula = "=COUNTIF(" & i & ":" & i & ","""")"
If ColNo > Cells(1, 5).Value Then
GoTo BreakNext
End If
Next i
BreakNext:
Cells(1, 6) = i
End Sub

I am using cells A5 to store the COUNTIF function and A6 to store
the last Rown No.