Find the last used Row on a Worksheet
 

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

Find the last used Row on a Worksheet
 

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" wrote:

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

Find the last used Row on a Worksheet
 

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" wrote:

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

Find the last used Row on a Worksheet
 

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" wrote:

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

Find the last used Row on a Worksheet
 

See
http://www.rondebruin.nl/last.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"kirkm" wrote in message ...
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

Find the last used Row on a Worksheet
 

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").Cell s) 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.

"kirkm" wrote:

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

Find the last used Row on a Worksheet
 

Thanks one and all for the help and
code exapmples. Much appreciated & problem solved.
Learnt some valuable stuff too :)
Cheers - Kirk

Find the last used Row on a Worksheet
 

On Apr 29, 11:35 pm, kirkm
wrote:
Thanks one and all for the help and
code exapmples. Much appreciated & problem solved.
Learnt some valuable stuff too :)
Cheers - Kirk

Dim rngLastRow as Excel.Range

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

Find the last used Row on a Worksheet
 

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.


"ilia" wrote:

On Apr 29, 11:35 pm, kirkm
wrote:
Thanks one and all for the help and
code exapmples. Much appreciated & problem solved.
Learnt some valuable stuff too :)
Cheers - Kirk

Dim rngLastRow as Excel.Range

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

Find the last used Row on a Worksheet
 

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.

"ilia" wrote:

On Apr 29, 11:35 pm, kirkm
wrote:
Thanks one and all for the help and
code exapmples. Much appreciated & problem solved.
Learnt some valuable stuff too :)
Cheers - Kirk

Dim rngLastRow as Excel.Range

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

Find the last used Row on a Worksheet
 

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.



"ilia" wrote:

On Apr 29, 11:35 pm, kirkm
wrote:
Thanks one and all for the help and
code exapmples. Much appreciated & problem solved.
Learnt some valuable stuff too :)
Cheers - Kirk

Dim rngLastRow as Excel.Range

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

Find the last used Row on a Worksheet
 

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.



"ilia" wrote:

On Apr 29, 11:35 pm, kirkm
wrote:
Thanks one and all for the help and
code exapmples. Much appreciated & problem solved.
Learnt some valuable stuff too :)
Cheers - Kirk

Dim rngLastRow as Excel.Range

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