Determine the last used row in a sheet

For looping concerns we constantly find ourselves trying to invent quicker/more robust ways to find the last used row. So far, this is what the ninja has for you.

Sub find_maxrow()

‘find maxrow on sheet
‘lastrow = maxrow(insert sheet name in quotes or sheet number)

lastrow = maxrow(2)

End Sub

Function maxrow(maxrow_sheet As Variant) As Double

Dim curr_sheet As String
curr_sheet = ActiveSheet.Name ‘id the curr sheet so i know where to come back to

Sheets(maxrow_sheet).Select ‘select the sheet to find maxrow

cnt = 1 ‘start with col 1 and loop until usedrange.columns
Do While cnt < ActiveSheet.UsedRange.Columns.Count + 1 If (Range(Cells(ActiveSheet.Rows.Count, cnt), Cells(ActiveSheet.Rows.Count, cnt)).End(xlUp).Row) > maxrow Then
maxrow = Range(Cells(ActiveSheet.Rows.Count, cnt), Cells(ActiveSheet.Rows.Count, cnt)).End(xlUp).Row
End If
cnt = cnt + 1
Loop
Sheets(curr_sheet).Select ‘return to orig sheet

End Function

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: