Deleteing blank or "zero" cells

  • Thread starter Thread starter Alain Dekker
  • Start date Start date
A

Alain Dekker

Hi,

If I paste a long list of numbers that contains several blank cells, or
cells with a particular value in them (such as "0") that I want to delete,
how do I remove all these rows and create a new column with only the
unaffected values (.ie. non-blank, non-zero), without gaps?

Thanks,
Alain
 
If you want to do this without using VB code:
* use an empty column to store consecutive numbers
1,2,3... (use autofill)
* select the entire sheet and sort ascending on the column
that contains your actual data
* zero lines are on top (or in the middle, if you also
have negative values), and empty rows are at the end. It
is now easy to delete the rows.
* select the entire sheet again and sort ascending on the
row counter column you added. This will bring the row
sequence to the original order.
* you no longer need the row counters, so you can now
delete them.

If this is a repeated task, it is fairly easy and quite
worthy to record a macro to do it.

The alternative is to writre a piece of code to browse
through each row, check the cell in the column in question
and delete the row if blank or 0. If you're willing to get
into this I could give you sample code.

Nikos Y.
 
If there's a huge amount of rows, I like Nikos' answer.

but if there's not too many (experiment to find out what that means!).

You can select your column.
data|filter|autofilter
use the dropdown to show only the rows to delete
Delete those visible rows.

Data|filter|autofilter once more to get rid of the autofilter.
 
Back
Top