Have you ever wanted to test a group of columns for null or zero value cells and if there is nothing in that group of columns then delete the entire row. I came across this problem with a worksheet I was working on recently and used my well-worn and trusted autofilter to remove those unwanted columns. This was done with the assistance of a formula. To get over the line I needed a sumproduct.
=SUMPRODUCT((LEN(B2:N2)>0)*(B2:N2<>0))
Using the above formula in code should work nicely. Just set the range and assign the formula to the range. It will check the columns from B to N for both blanks and zeros. If there is data in the cells with values then a 1 will appear in the helper column we are creating. Otherwise there will be a zero. The following is the code which manages to achieve the task. It is using column 15 or Column O as the helper column. The formula is pushed into column O from row 2 to the last used row and then those cells which are equal to zero are deleted in one swift movement with the aid of the autofilter. After this is done the entire column is deleted.
Option Explicit
Sub DelBlankRow()
Dimrng As Range
Set rng = Range(Cells(1, 15), Cells([a1].CurrentRegion.Rows.Count, 15))
rng.Offset(1) = "=SUMPRODUCT((LEN(B2:N2)>0)*(B2:N2<>0))"
rng.AutoFilter 1, 0
rng.Offset(1).EntireRow.Delete
rng.EntireColumn.Delete
End Sub
Setting up the range is key at the start. It will help as the range is required to be called upon many times. The attached file is an example of the process which will remove a row of blank and/or zero values. It is fast and easy to use.
