What is the best way to find the last row that is not empty on a
spreadsheet, even if some columns (such as A) might be empty? Thanks,
James

Attached is a function that returns the true last cell in the sheet. You
could use it like this to get the last row (run Sub Test)...

Sub test()
MsgBox LastCell().Row
End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function

--
HTH...

Jim Thomlinson


"Zone" wrote:

What is the best way to find the last row that is not empty on a
spreadsheet, even if some columns (such as A) might be empty? Thanks,
James

Hmmm, I'm just a bit curious: how does the code below differ from the Excel
property ActiveSheet.UsedRange? Is it that the UsedRange prop takes e.g.
formatting into consideration as well, while your algorithm doesn't? That is,
is the purpose of your code to be flexible wrt how you define "true" cell
content (e.g., formulas are considered to be content, while formatting is
not)?

Cheers,
/MP

"Jim Thomlinson" wrote:

Attached is a function that returns the true last cell in the sheet. You
could use it like this to get the last row (run Sub Test)...

Sub test()
MsgBox LastCell().Row
End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function

--
HTH...

Jim Thomlinson


"Zone" wrote:

What is the best way to find the last row that is not empty on a
spreadsheet, even if some columns (such as A) might be empty? Thanks,
James

Used range is not always what you might think it would be. For instance if
you use cells below the last cell in the used range then the used range will
expand to encompass the larger area. However if you then delete those cells
that were just added the used range will not always contract back, until you
save the spreadsheet. Also sometimes the used arnge will encompass cells
which are actually empty. They were used at one point but they are not used
any longer. Even after you save the used range will still refer to these
empty cells (they were instantiated but never destroyed). The way to fix it
is to delete (not clear contents) those rows or columns and then save the
workbook to contract the used range again. One thing you may have seen at
some point in your travels is a sheet where the scroll bar scolls all the way
to row 65,536 despite there being now data beyond say row 100. In this case
the used range is all the way to 65,536.

The thing to note is that the used range will always be at least as large as
the data area, BUT sometimes it will be larger. About the only time I use the
used range is if I need to limit the size of a range supplied by the end
user. For example if I am dealing with the range returned by a ref edit where
the user may have chosen an entire column, I will limit that by using the
intersection of the selected column with the used range thereby making my
code more efficient.
--
HTH...

Jim Thomlinson


"Mat P:son" wrote:

Hmmm, I'm just a bit curious: how does the code below differ from the Excel
property ActiveSheet.UsedRange? Is it that the UsedRange prop takes e.g.
formatting into consideration as well, while your algorithm doesn't? That is,
is the purpose of your code to be flexible wrt how you define "true" cell
content (e.g., formulas are considered to be content, while formatting is
not)?

Cheers,
/MP

"Jim Thomlinson" wrote:

Attached is a function that returns the true last cell in the sheet. You
could use it like this to get the last row (run Sub Test)...

Sub test()
MsgBox LastCell().Row
End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function

--
HTH...

Jim Thomlinson


"Zone" wrote:

What is the best way to find the last row that is not empty on a
spreadsheet, even if some columns (such as A) might be empty? Thanks,
James

Ahhh, good to know -- I'll try to keep this in mind so I won't bang my head
for too long against the wall if/when I run into these sort of issues in the
future... :o)

Cheers Jim,
/MP

"Jim Thomlinson" wrote:

Used range is not always what you might think it would be. For instance if
you use cells below the last cell in the used range then the used range will
expand to encompass the larger area. However if you then delete those cells
that were just added the used range will not always contract back, until you
save the spreadsheet. Also sometimes the used arnge will encompass cells
which are actually empty. They were used at one point but they are not used
any longer. Even after you save the used range will still refer to these
empty cells (they were instantiated but never destroyed). The way to fix it
is to delete (not clear contents) those rows or columns and then save the
workbook to contract the used range again. One thing you may have seen at
some point in your travels is a sheet where the scroll bar scolls all the way
to row 65,536 despite there being now data beyond say row 100. In this case
the used range is all the way to 65,536.

The thing to note is that the used range will always be at least as large as
the data area, BUT sometimes it will be larger. About the only time I use the
used range is if I need to limit the size of a range supplied by the end
user. For example if I am dealing with the range returned by a ref edit where
the user may have chosen an entire column, I will limit that by using the
intersection of the selected column with the used range thereby making my
code more efficient.
--
HTH...

Jim Thomlinson


"Mat P:son" wrote:

Hmmm, I'm just a bit curious: how does the code below differ from the Excel
property ActiveSheet.UsedRange? Is it that the UsedRange prop takes e.g.
formatting into consideration as well, while your algorithm doesn't? That is,
is the purpose of your code to be flexible wrt how you define "true" cell
content (e.g., formulas are considered to be content, while formatting is
not)?

Cheers,
/MP

"Jim Thomlinson" wrote:

Attached is a function that returns the true last cell in the sheet. You
could use it like this to get the last row (run Sub Test)...

Sub test()
MsgBox LastCell().Row
End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function

--
HTH...

Jim Thomlinson


"Zone" wrote:

What is the best way to find the last row that is not empty on a
spreadsheet, even if some columns (such as A) might be empty? Thanks,
James

Thanks, Jim. Works perfect!
James