Forum Discussion
Deleting blank rows in excel..... but not deleting partially blank rows
Good day.
I'm not a novice in excel.
But this is quite an irritating task that I can't seem to get right with anyone's advice on the internet.
How can I delete blank rows in excel but not delete partially blank rows?
The advise given is on the Home tab> Editing> Find & Select> Go to Special> Blanks. Then under Home> Cells> Delete> Delete sheet rows.
This deletes partially blank rows as well.
Please help
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.
14 Replies
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.
I also have the same question.
I am not an expert by a longshot, but I do have to manage excel reports for work so it would help me to figure this out as well.
My reports are simple and don't use functions or queries. I just need to delete the rows that have no information at all. I've been unable to find step by step instructions for this which do not remove the rows with partial information. Any assistance would be appreciated. Thanks!
Ms. Burtis
copy your data to the sample attached and test it.
do not test directly on your work.. always have a back-up copy of your works.
hope this helps..
thanks
Hello,
Thanks that works in the document you submitted. How do I get it to work on my spreadsheet now? I tried to run it on a copy of work and it errors out (see attached).
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.AutoFilterI considered D as the last column and column E contains the formula of counta.
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.AutoFilterI considered D as the last column and column E contains the formula of counta.
Mr Douw,
How is your query doing?
If it is going nowhere, (I hope this is not a violation of forum policies.)
I am recommending some knowledgeable persons in the forum who can help you.
Misters Man Fai Chan, Matt Mickle, Wyn Hopkins, Jan Karel Pieterse, Jamil Mohammad (haven't heard from him though for quite some time now, but he is very helpful) .. to name a few.
Good luck..
