Forum Discussion
Deleting blank rows in excel..... but not deleting partially blank rows
Dear Community.
It was suggested that I should share the information that helped me on the forum so that everyone can benefit from it. I totally agree. So here it is:
Suppose your last column with data is column Z.
Add this formula to AA1:
=COUNTA(A1:Z1)
Double-click the fill handle to fill down (double-check if all rows are filled with the formula, if not, copy down as far as needed).
Now turn on filter and filter the table for the value of zero in that new column.
Use goto special, blanks now and do the delete. Then remove the filter.
I use an IF statement to maintain the integrity of the data. It will move all the blank rows to the bottom while making sure the other rows stay in order.
Let's assume there isn't 2000 rows of data (increase that number if there is)
Put the following into the A row of the first unused column (assume it's D for step 3)
Step 1
When 1 column must have a value i.e. Column A has user ID which can't be blank.
=IF(A1="", ROW(A1) + 2000, ROW(A1))
When random cells might contain Data you can check all the cells with the AND operator.
=IF(AND(A1="", B1="", C1=""), ROW(A1)+2000, ROW(A1))
(inside the AND brackets include all the cells you need to test)
Step 2
Autofill the column
Step 3:
Custom Sort on Column D (it will renumber the rows after the sort)
Step 4:
Delete Column D
As variant
=SUMPRODUCT( --NOT(ISBLANK(A1:INDEX(1:1, 1, COLUMN()-1) ) ) )drag down, filter and delete all rows which return zero in that column.