Databases Reference
In-Depth Information
Fix_it:
'Debug.Print "Error Description: "; Error; " - Error Code: "; Err
LastRow = 0
End Function
The methodology these functions employ to accomplish their tasks is not readily apparent to
even an experienced VBA programmer, so some explanation is in order here. What these functions
do is as follows. For finding the last row, the function starts searching for a nonempty cell by rows
from the bottom of the Worksheet
. As soon as the function encounters a nonempty cell (searching
by rows starting from the bottom), it knows this is the maximum row value for any data located
in this Worksheet. For finding the last column, the function starts searching for a nonempty cell
by columns
from the right side of the Worksheet
. As soon as the function encounters a nonempty
cell (searching by columns starting from the right), it knows this is the maximum column value
for any data located in this Worksheet.
Notice also that both of these functions have error-trapping mechanisms. This is because, should
a Worksheet not have any data in it, the find function will return an error (having not been able to
find anything). In this particular case, the last row or column value is set to 0 by the error-handling
mechanism, should they be tripped.
At the start of these functions, the Workbook and Worksheets to be acted upon are made the
active Workbook and the active Worksheet. The reason for this is that if the Worksheet and Workbook
are not active, the functions will not work. This is most likely a bug in the VBA environment.
Usually, if the Workbook and Worksheet to be acted upon are specified within the command, it is
not necessary that they be active. Case in point: the previous
EmptySheet
function will work
fine even if the sheet to be examined is not the active sheet.
Observe also that, once the position of the last row or column is found, it is selected and then
utilized in an R1C1 type of format. The previously defined functions (from Chapter 1) of
FindRow
and
FindCol
are able to then return the appropriate row or column number.
Finally, notice that these functions are declared as type Long. This is because Excel can have
over 64,000 rows in a Worksheet, and the integer data type has a maximum value of 32,767. It is
being mindful of little details such as this that make the difference between robust routines and
routines that are subject to failure.
It is just as likely to have the need to determine the last possible row that will contain data in
a particular column, or the last possible column that will contain data in a particular row. This can
easily be done by applying a
range
to the search for a nonempty cell as is shown in the next two
functions. Here, it is also necessary to add a third passed parameter to indicate which column or
row the determination will be made in.
Function LastRowinColX(Workbook, Worksheet, columnX As Integer)
As Long
'This function will return the last ROW in the Worksheet of Workbook
On Error GoTo Fix_it:
ActivateWorkbook (Workbook)
Sheets(Worksheet).Activate
Workbooks(Workbook).Worksheets(Worksheet) _
.Range(Cells(1, columnX), Cells(Rows.Count,
columnX))
. Find(What:="*",
_
SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Activate
LastRowinColX=FindRow(Selection.Address(ReferenceStyle:=xlR1C1))
Exit Function
Fix_it:
