Delete Table Rows using VBA

In this post, I am going to show you how to delete Excel table rows based on criteria using VBA.

Why did I need this?

I was invited to collaborate on an Excel file that is used by all of the Student Attendance Clerks at our campus. It is a file that I use in my job. I was excited to have the opportunity to potentially improve the file that was originally created in 2002. I will focus on one small part of the entire project in this post as I ended up revamping the entire file.

The Goal

Create a dynamic method to collect DeptID’s that are eventually used as filter criteria.

I wanted to use an Excel table. I think they are awesome! Unfortunately, not everyone knows how to use them. So I needed a way to “auto-magically” remove the rows that did not meet my requirements; a type of data validation if you will.

Collecting DeptID criteria – the old way

There is nothing wrong with the old way. Everyone got used to clicking the buttons just fine. My motivation to improve this process was simply could I do it better.

Collecting DeptID criteria – the new way

When the user understands how an Excel table works, the duration of the process is greatly decreased. The user just needs to type ‘Enter’ after each DeptID.

Old vs. New

The old way did not need to handle empty rows. Each ‘Add Depts’ controlled where the additional DeptID would be placed. Each ‘Delete Depts’ chose the row to be removed. In the new way, it became possible that blank rows may occur since I gave freedom to the user’s in the form of an Excel table. I needed to write VBA to fix the blank rows that may exist since it is likely the user will not always remember to ‘Delete Table Row’ when clearing the value from DeptID.

Excel table = ListObjects

ALWAYS name your Excel tables. Just get in that habit.

I named this table _deptIDCriteria. The prefix of the underscore is for another part of the whole project however, it works well for the next part where I show the VBA.

Public Sub deleteEmptyTableRows()

    Set deptCriteria = ThisWorkbook.Sheets("Sheet1").ListObjects("_deptIDCriteria")

    Dim i As Integer

    With deptCriteria
        For i = .ListRows.Count To 1 Step -1
            If Len(.ListRows(i).Range.Cells(1)) <= 3 Then
                If .ListRows.Count <= 1 Then
                    MsgBox ("You must enter at least 4 characters of the DeptID"), vbCritical
                    Exit Sub
                End If
                .ListRows(i).Delete
            End If
        Next i
    End With

End Sub

I modified this code from the actual code I used in the project however, it provides you with the framework for the operations.

The Result

The first IF actually deletes any row where the LEN of the Cell is less than 4; not just the empty table rows. It was my choice to delete these row values since it would return too many records from the query it actually filters against.

Bonus

Interested in the DynamicRowNum formula?

=ROW(_deptIDCriteria[@])-ROW(_deptIDCriteria[#Headers])