Excel VBA: Macro to Remove Matching Rows

Continuing on the theme of VBA macros in Excel, I will demonstrate a macro that I created today. The task at hand was to match class events in one database with another, and determine if the enrollment counts in database A were less than database B. Since there already exist online reports for both systems, it was decided that it would be easier to just compare the results where applicable.

The steps to do this are simple. First, get both reports. Then, run a simple query to insert the mapping of class ID’s between the two databases (I have an already existing Macro for this). The next step would be to create a VLOOKUP formula using the returned matching column. (For more information on using the VLOOKUP function, I recommend the following going here and here. She does an excellent job of describing the VLOOKUP worksheet function). Where there is an #N/A, delete the row since it is not applicable to our lookup. Below is the completed worksheet with VLOOKUP already plugged in. You can see in the formula where I am referencing another worksheet with the class ID mappings, and it is returning #N/A where there are no results.



There are two paths we can take to delete the rows with no matches. I can sort column G, and delete the grouped items all at once. There are several steps in doing this. I would need to sort by column G, then find the grouping, then select the group, then delete the group, and finally resort by class ID. However, since this is a common task I have to do frequently and I am incredibly lazy, I prefer the 1 button click solution to remove all of the rows with #N/A, so I will write a custom macro to do this for me. I have previously demonstrated how I write Excel macros in my previous article about Excel VBA, so I will skip this. Below is the final macro.

Sub DeleteMismatchFromVLookup()
    'Foundcell is the range returned from a search for a particular matching field, if nothing then search
    'did not return anything
    Dim found_cell As Range
            
    'Start at the top of the worksheet
    Range("A1").Select
    
    'Set the returned cell to the cell that is matching the search for #N/A
    Set found_cell = Cells.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            
    'If there are no matches, dont even bother running the rest of the macro
    If (Not found_cell Is Nothing) Then
        'Loop until no more matches are found in the worksheet
        Do
            'Activate the matching cell, then select the entire row and delete it
            found_cell.Activate
            Rows(ActiveCell.Row).Select
            Selection.Delete Shift:=xlUp
            
            'Do the search again. Do no activate until next loop to prevent trying to run
            'an objects method when no objects are found, causing an error
            Set found_cell = Cells.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
                                        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Loop While (Not found_cell Is Nothing)
    End If
    
    'Set found_Cell to nothing to free up any residually held memory
    Set found_cell = Nothing
End Sub

I hope this demonstrates that there are legitimate uses for VBA other than exposing your system to viruses. I have a whole library of macros such as this I use on a daily basis for working with spreadsheets. There are tons of libraries out there with Office Macros, such as Woody’s Office Power Pack.

Despite my praise for Offices Macro feature, I look forward to trying out the macros in OpenOffice. I am already using OO Calc for my budget, and I would like to break free of my dependence on Excel. Plus it would be nice to show my continuing support for FOSS and write an article about it. It also looks like OpenOffice is starting to catch up with its macro development scene.