Databases Reference
In-Depth Information
modification, the subroutine could be called several times if multiple columns need to be searched,
perhaps utilizing a looping structure to increment the passed parameter
cellcol%
.
Sub DeleteRowX(wkbook$, wksheet, cellcts$, cellcol%)
'Deletes Rows in Workbook (wkbook$) Sheet (wksheet$) which
contain (cellcts$) in
'Column Number (cellcol%)
Dim Total_Rows As Integer, rowptr As Integer
Dim ii As Integer
Total_Rows = TotalRows(wkbook$, wksheet)
rowptr = 1
For ii = 1 To Total_Rows
If Workbooks(wkbook$).Worksheets(wksheet).Cells(rowptr,
cellcol%) = cellcts$ Then
Rows(rowptr).Select
Selection.Delete Shift:=xlShiftUp
Else
rowptr = rowptr + 1
End If
Next ii
'ReSave the Workbook without the empty rows
Workbooks(wkbook$).Save
End Sub
To eliminate the standards in the preceding example, the following calls could be made within
the VB environment:
Call DeleteRowX("ELDReport.xls",1,"Bromoquinoline",1)
Call DeleteRowX("ELDReport.xls",1,"Chloramphenicol",1)
Call DeleteRowX("ELDReport.xls",1,"Nifuroxime",1)
This will eliminate the standards from the report, leaving only the data for the specific compounds
that have been run.
Suppose that it is also desirable to remove the “Position” and “Rerun” columns (labeled “B”
and “O,” respectively) from the Worksheet before it is to be uploaded to a database. In theory, no
two columns in a Worksheet should be assigned the same header. The
DeleteColumnX
subroutine
will delete the first column it finds with a header
(header$)
in row
(header_row%)
in
Worksheet(wksheet)
in
Workbook(wkbook$)
.
Sub DeleteColumnX(wkbook$, wksheet, header$, header_row%)
'Deletes a column which has header "header$" in row header_row%
For ii = 1 To 26
If Workbooks(wkbook$).Worksheets(wksheet).Cells(header_row%,
ii) = header$ Then
Workbooks(wkbook$).Worksheets(wksheet).Columns(ii).Delete
Exit Sub
End If
Next ii
End Sub
