Excel Macro Tutorial 3 – Remove header rows and delete junk loop

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:
bill report junk

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.

[ad name=”Synap blog wide”]

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

Excel Macro Tutorial 6 – Add macro buttons to your toolbar

Leave a Reply