Вообще-то в Excel-е есть очень полезное для тебя свойство рабочего листа UsedRange (задействованная область).
Вот как определяется последняя строка, содержащая текст:
Код:
Dim ws As Worksheet
Dim n As Integer
Set ws = Worksheets("Лист1")
n = ws.UsedRange.Row