Excel Macro Tutorial 3 – Remove header rows and delete junk loop
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.
The report has a bunch of junk we need to delete:
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.
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.
Excel Macro Tutorial 1 – Open a text file
Excel Macro Tutorial 2 – Formatting the Columns and Column headers
Excel Macro Tutorial 3 – Remove header rows and delete junk loop
Excel Macro Tutorial 4 – Delete non-chargeable tests and sort
Excel Macro Tutorial 5 – Delete cancelled tests, flag credited tests