Databases Reference
In-Depth Information
once it has been determined that the values returned by analyzing the standards fall within an
acceptable testing range (or window).
The first step required in setting up such a process is to determine the number of rows within
a Worksheet that contain data. The function
LastRow
accomplishes this task quite nicely.
Function LastRow(Workbook, Worksheet) 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).Cells.Find(What:="*
", _
SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Activate
LastRow = FindRow(Selection.Address(ReferenceStyle:=xlR1C1))
Exit Function
Fix_it:
'Debug.Print "Error Description: "; Error; " - Error Code: ";
Err
LastRow = 0
End Function
Here the Workbook name and the Worksheet name (or number) is passed to the function. The
methodology of this function is not readily apparent even to an experienced VBA programmer, so
some explanation is in order here. 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 from the bottom), it knows this is the maximum row value for any data point in this Worksheet.
Notice that this function has an error-trapping mechanism. 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 value is set to 0 by the error-handling mechanism should a
Worksheet be completely empty.
The final task at hand is to delete any rows in which one of the standards are present. To
accomplish this, a subroutine must “visit” each row in the Worksheet, and should that row contain
a standard, delete it. The problem here is that as the subroutine loops through the rows of the
Worksheet, as soon as a single row is deleted, the index of the loop counter no longer references
the next row of data in the Worksheet. This is because deleting a row caused all the data to shift
upward
by one row. The routine will now skip a row of data for each row that is to be deleted.
Hence, some rows will not be checked for the presence of a standard if a loop counter is utilized
as a row index. The solution to this problem is to use the loop only to propagate through the number
of rows to be checked, and to have a separate variable to be utilized as a pointer to the row of data
to be checked. If the last row checked was deleted, then the pointer is not incremented, as the next
row of data will automatically pop up to the current pointer position. If the last row checked was
not deleted, then the pointer is incremented.
The next subroutine
DeleteRowX
accomplishes this task quite nicely. The algorithm it utilizes
is as follows. The Workbook and Worksheet to be acted upon must be specified in the first two
passed parameters
wkbook$
and
wksheet,
respectively. A string,
cellcts$
, must be specified
that contains the contents which, if found in a cell, will cause the row to be deleted. In this
subroutine, only a single column is searched for the specified string
cellcts$
, which is specified
in the passed parameter cellcol%. The subroutine could just as easily be modified to search through
a number of columns for the specified string, should that prove necessary. As an alternative to
