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
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.
' 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, _
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
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
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!