Solutions and training for business users of Microsoft Excel.
Solutions and training for business users of Microsoft Excel.

 Excel User's Home
 ExcelUser Blog      
 Site Map              
 Contact              
 Excel for Business
 Excel Dashboards   
 Excel Solutions   
 Exploring Excel
 BI for Excel    
 Business Tools   
 Excel Catalog   
 Affiliate Program   
 Excel Help Portal  
 
   
     
   
     
 

Home > Exploring Excel

Use AutoFilter to Delete Rows
In Worksheets, Not Macros


"How do I do a macro that only deletes rows or columns containing certain data or words in an Excel database?" -- Shana G.  

Shana,

Is a macro really necessary?

This is how I typically respond whenever anyone asks how to write a macro for some purpose. In your case, I think that Excel's AutoFilter command would solve your problem much better than a macro would.

To illustrate, suppose you want to delete all Client Types with a value of "P" in the following table:

To set up the AutoFilter, select a cell in the table, say cell A1. Choose Data, Filter, AutoFilter. After you do so, Excel sets up buttons with down arrows for each header row, as shown below.

Because we want to delete the rows with a Client Type of P, we click on the Client Type arrow and select "P" from the dropdown list. After you do so, Excel displays only the requested rows of data. You can then select those rows and delete them.

Or, suppose you want to use a more complicated selection criteria. Suppose you want to delete all rows with a Client Type of C and that have a Type with "Cash" in the description.

To do so, first select the Client Type of "C" as shown above. Then select Custom from the dropdown list for the Type column. When you do so, Excel displays the Custom AutoFilter dialog:

Choose contains from the list as shown above. And enter Cash as shown. After you choose OK, Excel will display all rows with a Client Type of C and with a Type that contains "Cash". Excel would display only row 5 from the original display above, because this is the only row that meets our criteria.

In contrast, using a macro would create several problems, including:

1. You would need to set up ways to specify the selection criteria you want to use. As programmers say, that task could be non-trivial.

2. You would need to test the macro very well, because you don't want it to delete rows that you want to save.

3. You would need to document the macro so that others can use it when you move to a different department.

The macro could take hours of your time. The AutoFilter takes minutes.

Hope this helps,

Charley Kyd
July, 2006


 


ExcelUser, Inc.
http://www.ExcelUser.com

Copyright 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.

 

Excel Dashboards

Learn how to create top-quality dashboard reports with Excel.