I would like to manually search a long column for each cell with a non-
negative value. My intention is to delete each row with a cell that
meets that condition. Ostensibly, I would like to use Edit - Find
for this purpose. But I don't see how.

Is there any way (other than a macro) that I can find (move the cursor
to) each cell in a selected column with a non-negative value?

Take a look at ASAP Utilities......a free add-in available at
www.asap-utilities.com

Vaya con Dios,
Chuck, CABGx3



" wrote:

I would like to manually search a long column for each cell with a non-
negative value. My intention is to delete each row with a cell that
meets that condition. Ostensibly, I would like to use Edit - Find
for this purpose. But I don't see how.

Is there any way (other than a macro) that I can find (move the cursor
to) each cell in a selected column with a non-negative value?

On May 14, 6:14 am, "
wrote:
Is there any way (other than a macro) that I can find (move the cursor
to) each cell in a selected column with a non-negative value?

If this cannot be done without a macro, can someone offer a macro that
might move down a column starting with the selected column, selecting
(moving the cursor to) the next cell that has a non-negative value?

Apply datafilterautofilter, from dropdown select custom and less than 0
select all visible rows after you filtered the list and delete them (make
sure you select entire row), then clear the filter

You might want to do this on a copy to make sure you get it right


--
Regards,

Peo Sjoblom




wrote in message
ups.com...
On May 14, 6:14 am, "
wrote:
Is there any way (other than a macro) that I can find (move the cursor
to) each cell in a selected column with a non-negative value?

If this cannot be done without a macro, can someone offer a macro that
might move down a column starting with the selected column, selecting
(moving the cursor to) the next cell that has a non-negative value?

On May 14, 10:50 am, "Peo Sjoblom" wrote:
Apply datafilterautofilter, from dropdown select custom and less than 0
select all visible rows after you filtered the list and delete them (make
sure you select entire row), then clear the filter

Great! But this is the first time that I have used (Auto)Filter, and
something very odd happened. Well, perhaps it is a usage error. Can
you explain?

(I should say it __was__ "very odd" until I isolated the root cause.
See below.)

I select column B (clicking the column name "B"), then enable
AutoFilter as you describe. When I select the "All" criteria, the
AutoSum (lower-right of the window) is 1660.99. When I select Less
Than 0, the AutoSum is -12452.92. When I select Greater Than Or Equal
To 0, the AutoSum is 14113.11. The "14113.11" that is off (too low)
by 0.80.

The root cause appears to be that the first row is not filtered
"correctly" (i.e. according to selected custom criteria). B1 is
-0.80. So when I filter Greater Than Or Equal To 0, -0.80 is included
in the AutoSum of "positive" values.

Does AutoFilter expect (require) that the first row is a title row?

I do not see where that is stated explicitly in the Help page. But
the example does show a title row.

When I insert a blank row 1, everything is copacetic.

It helps if the you use a header and also that you select the whole range
before applying the filter just in case there can be some empty rows. Also
make sure all values are numerical, a text value of -0.80 is seen as greater
than any number.. You can test that by using

=ISNUMBER(A2)

where A2 would hold -0.80, if that returns FALSE it is text.
Then you can copy an empty cell, then select all number and do editpaste
special and select add.

But as I stated in my first post, make a backup copy.

--
Regards,

Peo Sjoblom


wrote in message
ps.com...
On May 14, 10:50 am, "Peo Sjoblom" wrote:
Apply datafilterautofilter, from dropdown select custom and less than 0
select all visible rows after you filtered the list and delete them (make
sure you select entire row), then clear the filter

Great! But this is the first time that I have used (Auto)Filter, and
something very odd happened. Well, perhaps it is a usage error. Can
you explain?

(I should say it __was__ "very odd" until I isolated the root cause.
See below.)

I select column B (clicking the column name "B"), then enable
AutoFilter as you describe. When I select the "All" criteria, the
AutoSum (lower-right of the window) is 1660.99. When I select Less
Than 0, the AutoSum is -12452.92. When I select Greater Than Or Equal
To 0, the AutoSum is 14113.11. The "14113.11" that is off (too low)
by 0.80.

The root cause appears to be that the first row is not filtered
"correctly" (i.e. according to selected custom criteria). B1 is
-0.80. So when I filter Greater Than Or Equal To 0, -0.80 is included
in the AutoSum of "positive" values.

Does AutoFilter expect (require) that the first row is a title row?

I do not see where that is stated explicitly in the Help page. But
the example does show a title row.

When I insert a blank row 1, everything is copacetic.

From help on autofilter.

"When you use the AutoFilter command, AutoFilter arrows appear to the right of
the column labels in the filtered range."


Note "column labels" means titles in row 1

A bit ambiguous and does not specifically state that autofilter results will not
include the cell with the arrow.


Gord Dibben MS Excel MVP

On 14 May 2007 16:36:02 -0700, "
wrote:

Does AutoFilter expect (require) that the first row is a title row?

I do not see where that is stated explicitly in the Help page. But
the example does show a title row.

On May 14, 6:41 pm, Gord Dibben <gorddibbATshawDOTca wrote:
From help on autofilter.
"When you use the AutoFilter command, AutoFilter arrows appear to the right of
the column labels in the filtered range."
Note "column labels" means titles in row 1
A bit ambiguous and does not specifically state that autofilter results will not
include the cell with the arrow.

Thanks for the confirmation. Makes sense now.