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.
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 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.
Interested in the DynamicRowNum formula?