Pre-format to eliminate lines with zero values
Posted by Angel on September 27, 2001 6:30 AM
I run several spreadsheets on a daily basis and copy and paste information from another file to spreadsheets. The copied area consists of three columns, product name, date and value. What I need to do is take out the lines (cells) with a value of zero in hopefully a preformatted method on the spreadsheets. Right now to "clean up" my report I actually go and delete cells! You know highlight the three cells to be deleted and move on to the next cells with no value. There has to be an easier way!!??
Posted by Ian on September 27, 2001 7:02 PM
I assume that you only need to check the "Value" column(assumed to be Column C) for zeroes.
Instead of just providing you with a macro (no learning process that way!), here's a way of doing it that can also be recorded in a macro that will work on any number of rows without having to adjust the code :
- Select from A2 to the last data row
(The above step needs to be done before running the macro)
- turn on the macro recorder
- insert a column
- Tpye IF(D20=0,0,"")
- Press Ctrl+Enter
- Go to Edit>GoTo>Special>Formulas>Numbers and click OK
- Go to Edit>Delete>EntireRow
- Go to Edit>Delete>EntireColumn
- turn off the macro recorder
