Ok now we’re getting into a bit more advanced material. We’re going to have the macro do some real grunt-work for us. Here’s what we want it to do:
1. Find the last row of the report.
2. Go through the entire report, row by row, and delete anything that we don’t want.
Simple enough, the only problem is you can’t just record yourself going through the report deleting stuff and then go back and change the code. Reason being you’re using your brain and eyeballs to identify what is junk and what is not, and the macro recorder does not record brain and eyeball activity.
[ad name=”Synap sidebar1″]
How to delete junk data from an Excel spreadsheet with a macro:
This is where you would have to dig out your reference book, list what you are trying to do as I have above, and come up with the code. This is what I came up with:
Sub JunkDelete() 'Get FinalRow 'Loop through each row. 'Examine for junk pattern 'Delete row if junk Dim intX As Integer Dim intFinalRow As Integer 'Final row intFinalRow = Range("A65536").End(xlUp).Row 'From 'VBA and Macros for Microsoft Excel by Bill Jelen intX = 2 Do While intX <= intFinalRow 'This for statement loops through each row on report one by one intFinalRow = Range("A65536").End(xlUp).Row 'From 'VBA and Macros for Microsoft Excel by Bill Jelen 'We start at row 2, because 1st row had headers we want to keep Dim strCellText As String 'Variable holds text read from first cell Dim blnJunk As Boolean 'If the row is declared junk, it will be deleted. blnJunk = False 'Assume the row to be not junk to start. strCellText = CStr(Cells(intX, 1).Value) 'Get whatever is in column A of current row. Debug.Print "String read from cell " & intX & " was: " & strCellText 'While just starting to write the loop, you can use debug.print statments to check. 'Now we do a series of comparisons to see if the row is junk. 'Check for dashes. If InStr(strCellText, "---------------------") > 0 Then 'Dashes 'InStr, short for "In String", looks to see if a certain string is found in another string. blnJunk = True End If 'Check for a blank If Trim(strCellText) = "" Then 'Blank 'Trim removes blank spaces from the front and end of a string, if the string is all blanks, you end up with nothing or "" blnJunk = True End If 'Check for footer item "REPORT_ID:" If InStr(strCellText, "REPORT_ID") > 0 Then 'Found it blnJunk = True End If 'Check for footer item "RETENTION:" If InStr(strCellText, "RETENTION") > 0 Then 'Found blnJunk = True End If 'Check for header item "Patient Name" If InStr(strCellText, "Patient Name") > 0 Then 'found blnJunk = True End If 'Check for footer item 'DATE:" If InStr(strCellText, "DATE:") > 0 Then 'found blnJunk = True End If 'Testing is over, if row got flagged Junk, we need to delete the current row If blnJunk = True Then 'Delete Rows(intX).Delete intX = intX - 1 'Since we deleted a row, we need to re-examine the row again, because excel moves the next row up a spot. End If 'The loop will restart at the top until every row has been examined. intX = intX + 1 'At the end of the loop we are down with the current row, so we increment the counter by one. Loop End Sub
Not too difficult after all. I used a While loop instead of my favored For Next loop because of the ever-changing FinalRow and current row. Also instead of a bunch of if statements I would have used a nested loop and an array, but there’s no need to complicate things here.
In the next tutorial we will handle sorting the list, looking for cancelled/charged-back tests and highlighting credited tests.