Find the last used Row on a Worksheet

  • Thread starter Thread starter kirkm
  • Start date Start date
K

kirkm

Here's an example I found on the web
---

Find the last used Row on a Worksheet:

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

---

How do you tell Sub FindLastRow what worksheet to use ?

Also, is the above an OK method ?

Thanks - Kirk
 
This was my solution last week. It take a couple of seconds to run, but it
is a straight forward way of finding last row that I can remember. I don't
like to remember code unless I use it often.

Sub Lastrow()

For RowCount = Rows.Count To 1 Step -1
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
If (LastCol <> 1) Or (Not IsEmpty(Cells(RowCount, "A"))) Then
Exit For
End If
Next RowCount
MyLastRow = RowCount
End Sub
 
in column A try

MsgBox ("") & Cells(65536, "A").End(xlUp).Row


"Joel" skrev:
This was my solution last week. It take a couple of seconds to run, but it
is a straight forward way of finding last row that I can remember. I don't
like to remember code unless I use it often.

Sub Lastrow()

For RowCount = Rows.Count To 1 Step -1
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
If (LastCol <> 1) Or (Not IsEmpty(Cells(RowCount, "A"))) Then
Exit For
End If
Next RowCount
MyLastRow = RowCount
End Sub

kirkm said:
Here's an example I found on the web
---

Find the last used Row on a Worksheet:

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

---

How do you tell Sub FindLastRow what worksheet to use ?

Also, is the above an OK method ?

Thanks - Kirk
Click to expand...
Click to expand...
 
or
MsgBox ("") & Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row

"Joel" skrev:
This was my solution last week. It take a couple of seconds to run, but it
is a straight forward way of finding last row that I can remember. I don't
like to remember code unless I use it often.

Sub Lastrow()

For RowCount = Rows.Count To 1 Step -1
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
If (LastCol <> 1) Or (Not IsEmpty(Cells(RowCount, "A"))) Then
Exit For
End If
Next RowCount
MyLastRow = RowCount
End Sub

kirkm said:
Here's an example I found on the web
---

Find the last used Row on a Worksheet:

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

---

How do you tell Sub FindLastRow what worksheet to use ?

Also, is the above an OK method ?

Thanks - Kirk
Click to expand...
Click to expand...
 
If you want to modify your code to search a specific sheet you need to
qualify your range references with the worksheet

Sub FindLastRow()
Dim LastRow As Long

If WorksheetFunction.CountA(Worksheets("Sheet2").Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Worksheets("Sheet2").Cells.Find(What:="*", _
After:=Worksheets("Sheet2").[A1], _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox LastRow
End If

End Sub


But, it is better to use a With statement for this

Sub FindLastRow()
Dim LastRow As Long

With Worksheets("Sheet2")
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 With
End Sub


Of course, this code will work on the active workbook. If you needed to
specify which workbook, you would need to qualify your range references
further.
 
Thanks one and all for the help and
code exapmples. Much appreciated & problem solved.
Learnt some valuable stuff too :)
Cheers - Kirk
 
Thanks one and all for the help and
code exapmples. Much appreciated & problem solved.
Learnt some valuable stuff too :)
Cheers - Kirk
Click to expand...

Dim rngLastRow as Excel.Range

With ActiveSheet
Set rngLastRow
= .Cells(.UsedRange.Rows.Count,.UsedRange.Columns.Count).EntireRow
End With
 
One caveat - the usedrange is not always the last cell that actually has data
in it. For example, if a cell below the last cell w/data has formatting
applied, the formatted cell will be the last cell in the usedrange even
though it is empty.
 
One caveat - the usedrange is not always the last cell that actually has data
in it. For example, if a cell below the last cell w/data has formatting
applied, the formatted cell will be the last cell in the usedrange even
though it is empty.

What could be problematic, is if the usedrange is say D5:F7 (as these are
the only cells that ever had any data in them), your code will select Row 3
(because there are only 3 rows in the usedrange). I think you would need to
change it to:

With ActiveSheet
Set rngLastRow =
..UsedRange.Cells(.UsedRange.Rows.Count,.UsedRange.Columns.Count).EntireRow
End With

but that assumes that the first caveat does not create any problems.
 
One caveat - the usedrange is not always the last cell that actually has data
in it. For example, if a cell below the last cell w/data has formatting
applied, the formatted cell will be the last cell in the usedrange even
though it is empty.

What could be problematic, is if the usedrange is say D5:F7 (as these are
the only cells that ever had any data in them), your code will select Row 3
(because there are only 3 rows in the usedrange). I think you would need to
change it to:

With ActiveSheet
Set rngLastRow =
..UsedRange.Cells(.UsedRange.Rows.Count,.UsedRange.Columns.Count).EntireRow
End With

but that assumes that the first caveat does not create any problems.
 
Hope I'm not multiposting today - darn browser keeps hanging

One caveat - the usedrange is not always the last cell that actually has
data in it. For example, if a cell below the last cell w/data has formatting
applied, the formatted cell will be the last cell in the usedrange even
though it is empty.

What could be problematic, is if the usedrange is say D5:F7 (as these are
the only cells that ever had any data in them), your code will select Row 3
(because there are only 3 rows in the usedrange). I think you would need to
change it to:

With ActiveSheet
Set rngLastRow =
..UsedRange.Cells(.UsedRange.Rows.Count,.UsedRange.Columns.Count).EntireRow
End With

but that assumes that the first caveat does not create any problems.
 
Back
Top