Hi Jez,
There are a few ways to get the last used row, some of which are faster or more reliable than others. This is my favourite function that I've written because it is very flexible (it can be used to find the last used row in a column, a portion of a worksheet or an entire worksheet):
Code:
Public Function GetLastRow(ByVal rngToCheck As Range) As Long
Dim rngLast As Range
Set rngLast = rngToCheck.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious)
If rngLast Is Nothing Then
GetLastRow = rngToCheck.Row
Else
GetLastRow = rngLast.Row
End If
End Function
If you place that function in a standard code module you can then call it from the Workbook_Open() event handler, for example:
Code:
Private Sub Workbook_Open()
Dim LastRow As Long
'get the last used row in Sheet1!C:C
LastRow = GetLastRow(Sheet1.Range("C:C"))
'put a value in the next row down
Sheet1.Cells(LastRow + 1, "C").Value = "new item " & (LastRow + 1)
End Sub
If you only ever need to find the last used row within a single column then a faster way is to use the Range.End(xlUp) property.
Hope that helps...
dBforums
>
Find Next Empty Row
03-01-10, 09:11
Linear Mode
