Google Answers Logo
View Question
Q: How do I do this in Excel? ( Answered 3 out of 5 stars,   4 Comments )
Subject: How do I do this in Excel?
Category: Computers > Software
Asked by: chanelops-ga
List Price: $10.00
Posted: 06 Jul 2002 05:51 PDT
Expires: 05 Aug 2002 05:51 PDT
Question ID: 37011
I'm using Excel to process a large amount of data, so I have some
spreadsheets that are big, thousands of rows.  After massaging the
data, I have generated new info that is stored in new columns, but
only for SOME of the rows.  For the rest of the rows, the cells in
this column have zeroes.  My question is, how can I easily take the
non-zero info in these new columns and consolidate it into a new
spreadsheet (or even a new section of this sheet) that doesn't have
any of the rows that had zeroes in the new columns?  The contents of
the cells are a mixture of text and numbers, if that matters.

I'm not sure how clear the above description is, but basically what I
want to do is to separately save only those rows that have info in the
new columns.  I'd rather not use macros if possible, but if that's the
only way, I'm open to doing that.

Request for Question Clarification by huntsman-ga on 06 Jul 2002 06:22 PDT
Request for Question Clarification -


What operating system and version of Excel are you running?

Can you give us a small example of what you're trying to do? Is it
something like the following, where you would strip out the zero cells
in Column A, then reconsolidate its remaining data into Column B?

| Column A | Column B |
| 123      | 123      |
| cat      | cat      |
| 789      | 789      |
| 0        | dog      |
| dog      | 678      |
| 678      | pig      |
| 0        | <empty>  |
| pig      | <empty>  |

Subject: Re: How do I do this in Excel?
Answered By: huntsman-ga on 06 Jul 2002 10:39 PDT
Rated:3 out of 5 stars

Since you are working with numbers and text, I think it will be
easiest if you use Excel macros to help move things around.
Using Microsoft Excel 2000 and its Visual Basic Editor, I recorded and
edited the following "RemoveZeroes" macro. It automatically strips
zeroes from a pre-selected range of data in a column, and consolidates
the remaining non-zero data. You can then copy and paste (or otherwise
move) the edited data elsewhere as desired.

If you have Excel 2000, you should be able to copy and paste the macro
text below into your own macro and run it.

If you have another version of Excel, note the comments
(non-executable lines starting with an apostrophe) within the macro.
These explain the macro's basic steps using Excel's Menu commands, and
should allow you to create a similar macro in your version of Excel.
Keep in mind that you may have to do some additional tweaking in your

Thank you,


Excel 2000 Macro -

Create a new macro in Excel 2000, name it "RemoveZeroes", and Edit it.
Copy the following text (excluding the "Sub" and "End Sub" lines) and
paste it into new macro.

Sub RemoveZeroes()
' Excel 2000
' RemoveZeroes Macro
' Macro created 7/6/2002 by huntsman
' Manually select the desired data range, and run this macro to:
' 1. Edit > Replace... 0 with nothing (leave Replace With... field
' 2. Edit > Go To... > Special... > select Blank Cells.
' 3. Edit > Delete > Shift Cells Up.
' 4. Select top cell in data range (VBA command).
' 5. Select edited data range (VBA command).
' Copy & paste the new data elsewhere as desired.
    Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Selection.Delete Shift:=xlUp
    Range(ActiveCell, ActiveCell.End(xlUp)).Select
End Sub


Macro Results -

When you run the "RemoveZeroes" macro on selected data, it should give
you results like this:

     Original Data     Edited Data
     ------------      ------------
     | 123      |      | 123      |
     | cat      |      | cat      |
     | 789      |      | 789      |
     | 0        |      | dog      |
     | dog      |      | 678      |
     | 678      |      | pig      |
     | 0        |      ------------
     | pig      |

Note that this particular macro works in place on the data range you
have selected. It does not move the selected and edited data into a
new column.


Additional References -

VBA Excel Tips: Moving Around

Search Terms & Google Results - 

I used the following searches to find a few basic ideas, then I
started experimenting within Excel.

excel ignore cells zeroes

excel select first empty cell +in range

Clarification of Answer by huntsman-ga on 06 Jul 2002 21:28 PDT

Sorting will work, but it involves several more manual steps, is less
accurate, less flexible, and more consistent. You will have to:

1. Sort column(s) to isolate the zero rows (this alone takes several
2. Manually select only the zero rows (without picking up any non-zero
3. Manually delete the zero rows.
4. Resort the column(s) back into the original order. 

Plus, you (or anyone else) will have to remember all these steps the
next time you need to do the same job.

The RemoveZeroes macro takes fewer steps, is more accurate, more
flexible, and more consistent. You can select an entire worksheet or
just part of it (multiple columns, one column, a partial column, or
non-adjacent data) and then simply run the macro.

The macro automatically selects *only* the zero rows (with no risk of
picking up any non-zero data), deletes them, and leaves your original
sort order untouched. Plus, it remembers all these steps for you, or
for anyone else that might need to do the same job.

You can also create a button on your Excel toolbar to easily run the
RemoveZeroes macro with a single click.

Take a test drive and compare!


Clarification of Answer by huntsman-ga on 06 Jul 2002 21:31 PDT
The first sentence in my clarification should read:

"Sorting will work, but it involves several more manual steps, is less
accurate, less flexible, and *less* consistent." 

chanelops-ga rated this answer:3 out of 5 stars
This answer works, and I appreciate the trouble you went to to write
the macro.  However, I think the better solution is the one proposed
by ulu -- just sort the data and toss the unwanted rows (which will
then all be together).

Subject: Re: How do I do this in Excel?
From: alienintelligence-ga on 06 Jul 2002 06:50 PDT
Can you use an IF THEN?

I sometimes use IF OldSheet!A1 <> "" THEN NewSheet!A1 = OldSheet!A1
ELSE NewSheet!A1 = ""

Then on the new sheet, if you "key" the rows,
you can sort the data in different ways, yet
the data is tied to the original sheet. And
you can return the original sort order, by
sorting on the keyed column. 

This isn't intended as an answer, just a req
for clarification of the problem.

Subject: Sort by that column?
From: ulu-ga on 06 Jul 2002 14:32 PDT
I'm no Excel expert, but you could probably sort that column and copy
out the non-zero entries.  (looks like alienintelligence suggested
Subject: Re: How do I do this in Excel?
From: rupert244-ga on 21 Aug 2002 16:57 PDT
Or add a filter:

1. select all data including titles.
   [get into top left cell, then shortcut Ctrl+Shift+End]

2. activate autofilter - Data->Filter->AutoFilter
   [shortcut Alt+D+F+F]

3. select drop down arrows to filter what you do/don't want
   [in your case probably "NonBlanks" or Custom->Does Not Equal -> 0]

   The rows you don't want will disappear = look at the [now blue] row
numbers on the left.

4. select table and copy paste to new workbook - it only copies
unifltered data.

The autofilter function is pretty useful - it allows filtering on more
than one column at once, and upto 2 conditions on each column.
Subject: Re: How do I do this in Excel?
From: chanelops-ga on 21 Aug 2002 17:58 PDT
That looks pretty neat, Rupert.  I'll have to actually try it out.  Thanks!

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy