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 -

chanelops,

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>  |
-----------------------

Thanks,
huntsman

chanelops, 

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
version.

Thank you,
huntsman

-----

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
blank).
' 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.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    Range(ActiveCell, ActiveCell.End(xlUp)).Select
    Selection.CurrentRegion.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 -

Excel-VBA-Access.Com
VBA Excel Tips: Moving Around
http://www.excel-vba-access.com/vba-excel-tips-moving.htm


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
://www.google.com/search?sourceid=navclient&q=excel+ignore+cells+zeroes

excel select first empty cell +in range
://www.google.com/search?sourceid=navclient&q=excel+select+first+cell+%2Bin+range

---

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

chanelops,

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
steps).
2. Manually select only the zero rows (without picking up any non-zero
data).
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!

huntsman

---

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." 

huntsman

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).

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.

-AI

I'm no Excel expert, but you could probably sort that column and copy
out the non-zero entries.  (looks like alienintelligence suggested
that)

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.

That looks pretty neat, Rupert.  I'll have to actually try it out.  Thanks!