Forum Discussion

Douw de Beer's avatar
Douw de Beer
Copper Contributor
Jun 20, 2018
Solved

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 p...
excel
  • Douw de Beer's avatar
    Douw de Beer
    Jul 04, 2018

    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.

Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
Aug 29, 2018

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

JBurtis's avatar
JBurtis
Copper Contributor
Aug 30, 2018

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).

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Aug 30, 2018

    Ms. Burtis

     

    1. copy your data to the file I attached below (overwrite the previous one)

    2. try it - if it worked then maybe you would want to save it as your filename.

    hope this helps.

    thank you.. 

    • Jeff Dalton's avatar
      Jeff Dalton
      Copper Contributor
      Oct 12, 2018
      Thank you Lorenzo Kim. Just starting with VBA myself and appreciate you base code.

      In your sample sheet how would you change the code to check only one column for either 0 or no data? For example, there will always be data in column A and B, but I want to either Hide or Delete an entire row if Column C = 0 or is blank.

Resources