Excel Macro: remove rows with a zero value using VBA
I have a script that imports data from Excel into my database, but I find it helpful to remove certain rows where there was no real value (pun intended! 😅).
By deleting these values, the import process will be faster!
My use-case often involves running an aggregated query on a specific field. If that field has a value of “zero,” then I don’t need to import the row in.
The original dataset came from a Cross Tab / Pivot Table and was transposed (I actually have a script for this). The resulting dataset- that I’ll be importing in- may have a lot of “zero” values and do not need to be brought into my database table.
How can I remove these rows with VBA Code?
I’m taking the approach of:
- Filter the column where the value is zero
- Delete these rows (which are the only ones visible)
I’ve found that if you first sort by a filtered value, then it will speed up the delete process!
The code is below, with the explanation under it. Initial comments:
- My worksheet is named “data.” You’ll have to adjust yours, or make the code dynamic
- My dataset is Columns A to N, with the potentially blank numeric data in column N
Explanation
- Rows 6 to 17 sorts the data
- Rows 20 and 21 format the value to be a number (“comma format”), and it will turn zero into “-”
- Row 22 filters on the column with numeric data (“field 14” represents Column N, where my numeric data is)
- Rows 25 to 27 actually deletes the rows. After highlighting row 2, I’m using the keyboard short of “while holding shift, press END then DOWN” (I wrote about a variation of this)
- Rows 30 and 31 are a personal preference of mine to “reset” the spreadsheet, having it ready for the user to perform next steps
This macro may not be as useful on its own, but could certainly be incorporated into a process where you’re automating the import of data from Excel into a database. I wrote a tutorial on importing data where this is used.
