Delete all negative numbers leaving only positive numbers
 

I have a column containing positive and negative numbers. I need a formula
that will delete all the negative numbers from the list leaving only the
positive ones.

the list is extremely long so it would help me enormously. I hope someone
can help

Regards

Basil

Answer: Delete all negative numbers leaving only positive numbers
 

Hi Basil,

I can definitely help you with that! Here's a step-by-step guide on how to delete all negative numbers in a column using a formula:

1. Select the cell where you want to display the positive numbers.
2. Type the following formula:
   Code:

   ---

   =IF(A1<0,"",A1)

   ---

3. Replace "A1" with the cell reference of the first cell in the column that contains the numbers you want to filter.
4. Press Enter.
5. Copy the formula down to the rest of the cells in the column by dragging the fill handle (the small square at the bottom right corner of the cell) down to the last cell.
6. The formula will display the positive numbers in the selected cell and leave the negative numbers blank.
7. Select the column that contains the original list of numbers.
8. Press Ctrl + C to copy the column.
9. Right-click on the selected cell where the positive numbers are displayed.
10. Click on "Paste Special".
11. In the "Paste Special" dialog box, select "Values" and click "OK".
12. This will paste only the positive numbers into the selected cell.

That's it! You now have a column with only positive numbers. Let me know if you have any questions or if there's anything else I can help you with.

Delete all negative numbers leaving only positive numbers
 

If your list is in column A, then in B1 enter:
=IF(A1=0,A1,"")
and copy down. Column B will have the negatives cleared. If you like, you
can copy column B and pastespecial/value back onto column A

--
Gary''s Student - gsnu200754


"Barry Walker" wrote:

I have a column containing positive and negative numbers. I need a formula
that will delete all the negative numbers from the list leaving only the
positive ones.

the list is extremely long so it would help me enormously. I hope someone
can help

Regards

Basil

Delete all negative numbers leaving only positive numbers
 

Ahh yes,

I did forget to mention that the column of numbers relates to other columns
with corresponding information in them. So i need the formula to delete the
whole row relating to the negative number for all the negative numbers
leaving only positive numbers with their corresponding row information.

Sorry about that, can you still help?

"Gary''s Student" wrote:

If your list is in column A, then in B1 enter:
=IF(A1=0,A1,"")
and copy down. Column B will have the negatives cleared. If you like, you
can copy column B and pastespecial/value back onto column A

--
Gary''s Student - gsnu200754


"Barry Walker" wrote:

I have a column containing positive and negative numbers. I need a formula
that will delete all the negative numbers from the list leaving only the
positive ones.

the list is extremely long so it would help me enormously. I hope someone
can help

Regards

Basil

Delete all negative numbers leaving only positive numbers
 

right click the sheet tab, view code and paste this in and run it. It looks
for a zero value in column A and deletes the entire row if it finds one.

Sub sonic()
lastrow = Range("A65536").End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, 1).Value = 0 Then
Rows(x).EntireRow.Delete
End If
Next
End Sub

Mike


"Barry Walker" wrote:

Ahh yes,

I did forget to mention that the column of numbers relates to other columns
with corresponding information in them. So i need the formula to delete the
whole row relating to the negative number for all the negative numbers
leaving only positive numbers with their corresponding row information.

Sorry about that, can you still help?

"Gary''s Student" wrote:

If your list is in column A, then in B1 enter:
=IF(A1=0,A1,"")
and copy down. Column B will have the negatives cleared. If you like, you
can copy column B and pastespecial/value back onto column A

--
Gary''s Student - gsnu200754


"Barry Walker" wrote:

I have a column containing positive and negative numbers. I need a formula
that will delete all the negative numbers from the list leaving only the
positive ones.

the list is extremely long so it would help me enormously. I hope someone
can help

Regards

Basil

Delete all negative numbers leaving only positive numbers
 

I really meant this

If Cells(x, 1).Value < 0 Then

Mike

"Mike H" wrote:

right click the sheet tab, view code and paste this in and run it. It looks
for a zero value in column A and deletes the entire row if it finds one.

Sub sonic()
lastrow = Range("A65536").End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, 1).Value = 0 Then
Rows(x).EntireRow.Delete
End If
Next
End Sub

Mike


"Barry Walker" wrote:

Ahh yes,

I did forget to mention that the column of numbers relates to other columns
with corresponding information in them. So i need the formula to delete the
whole row relating to the negative number for all the negative numbers
leaving only positive numbers with their corresponding row information.

Sorry about that, can you still help?

"Gary''s Student" wrote:

If your list is in column A, then in B1 enter:
=IF(A1=0,A1,"")
and copy down. Column B will have the negatives cleared. If you like, you
can copy column B and pastespecial/value back onto column A

--
Gary''s Student - gsnu200754


"Barry Walker" wrote:

I have a column containing positive and negative numbers. I need a formula
that will delete all the negative numbers from the list leaving only the
positive ones.

the list is extremely long so it would help me enormously. I hope someone
can help

Regards

Basil

Delete all negative numbers leaving only positive numbers
 

Basil,

Sort your entire list based on that column, then select all the negative numbers and use Edit /
Delete.. Entirerow.

If you need to maintain the original order, insert another column, enter 1 in the first cell, 2 in
the second, select both cells and drag down, then do the sort/delete, and then resort based on the
inserted column to restore the original order. Then get rid of that column.

HTH,
Bernie
MS Excel MVP


"Barry Walker" wrote in message
...
I have a column containing positive and negative numbers. I need a formula
that will delete all the negative numbers from the list leaving only the
positive ones.

the list is extremely long so it would help me enormously. I hope someone
can help

Regards

Basil

Delete all negative numbers leaving only positive numbers
 

Filter your data using Data-Filter-Aoutfilter. Excel offers the SUBTOTAL
function which will ignore the hidden cells. You can do a lot of stuff using
the SUBTOTAL function (like AVERAGE, SUM, MIN, MAX etc.).

"Barry Walker" wrote:

Ahh yes,

I did forget to mention that the column of numbers relates to other columns
with corresponding information in them. So i need the formula to delete the
whole row relating to the negative number for all the negative numbers
leaving only positive numbers with their corresponding row information.

Sorry about that, can you still help?

"Gary''s Student" wrote:

If your list is in column A, then in B1 enter:
=IF(A1=0,A1,"")
and copy down. Column B will have the negatives cleared. If you like, you
can copy column B and pastespecial/value back onto column A

--
Gary''s Student - gsnu200754


"Barry Walker" wrote:

I have a column containing positive and negative numbers. I need a formula
that will delete all the negative numbers from the list leaving only the
positive ones.

the list is extremely long so it would help me enormously. I hope someone
can help

Regards

Basil

Delete all negative numbers leaving only positive numbers
 

Select the full range of data, then apply an auto-filter. Filter to show
the negative numbers (using the Custom option in the filter), and then
Delete Row. As ever, safest to save a copy before you try this.
--
David Biddulph

"Barry Walker" wrote in message
...
Ahh yes,

I did forget to mention that the column of numbers relates to other
columns
with corresponding information in them. So i need the formula to delete
the
whole row relating to the negative number for all the negative numbers
leaving only positive numbers with their corresponding row information.

Sorry about that, can you still help?

"Gary''s Student" wrote:

If your list is in column A, then in B1 enter:
=IF(A1=0,A1,"")
and copy down. Column B will have the negatives cleared. If you like,
you
can copy column B and pastespecial/value back onto column A

--
Gary''s Student - gsnu200754


"Barry Walker" wrote:

I have a column containing positive and negative numbers. I need a
formula
that will delete all the negative numbers from the list leaving only
the
positive ones.

the list is extremely long so it would help me enormously. I hope
someone
can help

Regards

Basil

Delete all negative numbers leaving only positive numbers
 

Delete the row or clear the row??
--
Gary''s Student - gsnu200755


"Barry Walker" wrote:

Ahh yes,

I did forget to mention that the column of numbers relates to other columns
with corresponding information in them. So i need the formula to delete the
whole row relating to the negative number for all the negative numbers
leaving only positive numbers with their corresponding row information.

Sorry about that, can you still help?

"Gary''s Student" wrote:

If your list is in column A, then in B1 enter:
=IF(A1=0,A1,"")
and copy down. Column B will have the negatives cleared. If you like, you
can copy column B and pastespecial/value back onto column A

--
Gary''s Student - gsnu200754


"Barry Walker" wrote:

I have a column containing positive and negative numbers. I need a formula
that will delete all the negative numbers from the list leaving only the
positive ones.

the list is extremely long so it would help me enormously. I hope someone
can help

Regards

Basil