Solved

Delete Rows in Excel

Posted on 2012-12-26

Microsoft Excel

1 solution

179 Views

Last Modified: 2012-12-27

I have an accounting worksheet where the names of the account are populated using a lookup to a master data set. This lookup leaves the value 0 (zero) for any account number that does not exist in the master data set. So now I want code that will delete all rows where the value in the row for column A is 0 (zero).

Comment

Question by:rawilken

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

3 Comments

LVL 13

Accepted Solution

by:Yagya Shree

Yagya Shree earned 100 total points

ID: 387214262012-12-26

You want to look into below post

http://www.extendoffice.com/documents/excel/815-excel-remove-rows-based-on-cell-value.html

LVL 39

Expert Comment

by:nutsch

ID: 387214272012-12-26

Assuming your data starts in cell A1, and your column A is formatted as General, you can use the following autofilter code:

with Cells(1,1).currentregion
.autofilter
.autofilter field:=1, criteria1:="0"
.offset(1).resize(.rows.count-1).specialcells(xlcelltypevisible).entirerow.delete
.autofilter
end with

Open in new window

LVL 43

Expert Comment

by:Saqib Husain, Syed

ID: 387214952012-12-26

If you want to do this again and again then the above code would be just fine.

If you need this only once then it would be more convenient to apply Data > Filter

and then filter out the zero values and delete them.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An overview of using NPV and IRR in Excel for evaluating investment decisions

Article by: Dave

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…

Microsoft Excel

Automated Link Checker

Article by: Josh

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…

Graph Data with Longer Labels in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

Microsoft Excel

How to use the IF function in Excel

Video by: Bob

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…