Archive of Mr Excel Message Board


Back to Data in Excel archive index
Back to archive home

Deleting Rows

Posted by Hanna on January 15, 2002 1:34 PM
Can someone please help me with the code for the following:

I want to delete all rows where the cell in column A is empty. It should do this for the selected area only.

Any ideas? Thanks :-)


Re: Deleting Rows

Posted by Chris D on January 15, 2002 1:45 PM
Have you tried autofilter ?

Highlight your entire range including column headings and select :

data
filter
autofilter

then on the column where you have your blanks, click on the downarrow

from the dropdown list, select "blanks"

this will filter out anything that isn't blank in that column

then highlight all those rows and right click and select :

delete rows

then remove your filtering and it should have deleted your rows which contained blanks in that column

HTH
Chris


Re: Deleting Rows

Posted by Qroozn on January 15, 2002 1:46 PM
I was going to recommend a manual way rather than a macro for this problem

Re: Deleting Rows

Posted by Chris D on January 15, 2002 1:54 PM
Oooops

I didn't see the word "code" in the question, I do apologise Qroozn.

Go for it - my VBA is totally woeful, hence me hanging on to manual solutions !

:-)


Re: Deleting Rows

Posted by Pandulph on January 15, 2002 2:35 PM

On Error Resume Next
Intersect(Selection, Columns("A:A")).SpecialCells(xlCellTypeBlanks).EntireRow.Delete



Re: Deleting Rows

Posted by Qroozn on January 15, 2002 3:11 PM
I didn't give coed beacuse the manual way is much easier i think.. i believe stay away from script unless it is necessary.

Or ....

Posted by Pandulph on January 15, 2002 4:07 PM
..... like this :-

Select Column A
Go to Edit>GoTo>Special>Blanks>OK
Go to Edit>Delete...>EntireRow


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.