Finding Row number of last active cell

[dullboy]

Hi,

I need to find the last row that contains data from within my macro.

I tried the rows.count - but this returns 65000, the max rows of a sheet.

Cheers,

 

[Richie(UK)]

Hi dullboy,

Try the following:

<pre>Sub FindLastRow()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox LastRow
End If
End Sub</pre>

Taken from this site: http://www.ozgrid.com/

HTH

 

[dullboy]

Cheers mate!

 

[dullboy]

That works great but is there some way I can pass the row number into a variable defined in the sub where it was called from - something like :-

numRows = FindLastRow

Also I would like to be able to pass in a value so it could be used for a different woorkbook. Something like :-

numRows = FindLastRow(1)

then in the sub :-

sub FindLastRow(WBnum)
If Workbooks(WBnum).WorksheetFunction.CountA(Cells) > 0 Then

Cheers,
DB

 

[Richie(UK)]

Hi DB,

You need to make the FindLastRow sub into a function. I've knocked one together and done a quick test - have a go and change to suit.

<pre>Function FindLastRow(wbkFullPath As String, wksName As String) As Long

On Error GoTo NoWBK
Workbooks.Open (wbkFullPath)
On Error GoTo 0

On Error GoTo NoWKS
With ActiveWorkbook.Worksheets(wksName)
On Error GoTo 0
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
FindLastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
End With

Exit Function
NoWBK:
MsgBox "Workbook not found!"
Exit Function
NoWKS:
MsgBox "Worksheet not found!"
Exit Function

End Function

Sub Test()
Dim LastRow As Long, wbkFullPath As String, wksName As String

wbkFullPath = "C:My DocumentsSpreadsheetsEntDate Save Test.xls"
wksName = "Sheet1"

LastRow = FindLastRow(wbkFullPath, wksName)
MsgBox LastRow

End Sub</pre>
HTH

 

[dullboy]

cheers mate - made a couple of changes and its sorted.

Regards,