MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Deleting rows if a particular cell is blank

Posted by Mandy on August 15, 2001 4:48 AM

Does any one know how to delete the contents of an entire row if a particular cell in that row is blank;
i.e. if c2 is empty then the rest of the cells in row 2 (some of them have formula in!) are deleted [not removed!]

Thanks to anyone who can shed light on this

All the best

Posted by Andrew Cheung on August 15, 2001 5:59 AM

The Macro should be as follow:

sub clear_balnk_rows ()
For r = 3 to 200 'Specify the number of rows you want
if Cells(r,1)="" then rows(r).clearcontents 'if cells(r,1) From C1 is blank, then the contents of the entire row would be cleared
next r
end sub

Posted by Mandy on August 15, 2001 6:22 AM

Big thankyou

Thanks very much; a very simple and elegant solution