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.
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.
Dear Douw de Beer,
It is an interesting way. Thanks for your sharing. I helped Mr Kim in his project before but did not think this way of solution. I recorded the macro as below:
Cells.Select
Selection.AutoFilter
Range("H9").Select
ActiveSheet.Range("$A$1:$E$20").AutoFilter Field:=5, Criteria1:="0"
Rows("4:20").Select
Selection.Delete Shift:=xlUp
Range("C21").Select
Selection.AutoFilter
I considered D as the last column and column E contains the formula of counta.