The billing report has a bunch of tests with at cpt code of ‘0’ or a blank.  These are non-chargeable tests.  We used to have to delete them manually.

For this we can loop through the rows and delete non-chargeables just like we did with the junk removal.  For efficiency we could just add this code to the junk-removal macro, but I have kept them separate here for the sake of simplicity. I called the macro ‘DeleteZero’ and copied and modified from the last macro:

How to delete blank rows (and rows with 0 value) with an Excel macro:

Sub DeleteZero()
'Get FinalRow
'Loop through each row.
'Examine for 0 cpt
'Delete row if 0

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
    'What is Cells?  This is just a different way of referring to excel cells.  Range("A1") is the same as Cells(1,1).
    'In the second part of this statement, we check for a blank cell by getting the value, convert it to a string, and trim it.
    If Cells(intX, 4).Value = 0 Or Trim(CStr(Cells(intX, 4).Value)) = "" 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

Now we’re supposed to sort by Patient name and Service Date.  Seems like it would be so easy.  I record my sort with the macro recorder:

Sub Sort()
'
' Sort Macro
'

'
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A140") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H2:H140") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:H140")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Uh-oh looks scary. Well there is it’s not too difficult to do this, but you probably would have had to study for awhile:

Sub Sort()
'
' Sort Macro
'

    'Lets walk through each line it recorded and make the changes.
    ActiveWorkbook.Worksheets(1).Range("A1").Select
    ActiveSheet.Sort.SortFields.Clear

    'See the problem here, it hard-coded in '140' as the last row.  Tomorrow the report may be '230' rows long.
    'Excel is smart enough to automatically detect the last row when you were doing the sort, why does it not record that? No Idea.
    intFinalRow = Range("A65536").End(xlUp).Row 'From 'VBA and Macros for Microsoft Excel by Bill Jelen

    'Going to use Cells(#,#) instead of ("A#:B#") again.
    'When you specify a range in A1 style it looks like: "Range("A2:A140")"
    'with cells it looks like this: "Range(Cells(2,1), Cells(140,1))"  - Note cells are separated by a comma and A1 style by a colon.
    'Dont get confused, with Cells the ROW comes first, then the COLUMN.  A1 notation is reversed.
    ActiveSheet.Sort.SortFields.Add Key:=Range(Cells(2, 1), Cells(intFinalRow, 1)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    ActiveSheet.Sort.SortFields.Add Key:=Range(Cells(2, 8), Cells(intFinalRow, 8)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    'We could use that FinalRow formula to get the last row, but there is an easier way.
    'Since the data is contiguous (no blank rows/columns between sections of the report) we can use CurrentRegion.
    With ActiveSheet.Sort
        .SetRange ActiveCell.CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

In the next tutorial we will tackle the tricky stuff, finding cancelled tests and flagging chargebacks!

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