A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Delete all negative numbers leaving only positive numbers



 
 
Thread Tools Display Modes
  #1  
Old November 8th 07, 03:01 PM posted to microsoft.public.excel.misc
Barry Walker
external usenet poster
 
Posts: 31
Default 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
Ads
  #2  
Old November 8th 07, 03:09 PM posted to microsoft.public.excel.misc
Gary''s Student
external usenet poster
 
Posts: 11,059
Default 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

  #3  
Old November 8th 07, 03:22 PM posted to microsoft.public.excel.misc
Barry Walker
external usenet poster
 
Posts: 31
Default 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

  #4  
Old November 8th 07, 03:40 PM posted to microsoft.public.excel.misc
Mike H
external usenet poster
 
Posts: 11,504
Default 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

  #5  
Old November 8th 07, 03:42 PM posted to microsoft.public.excel.misc
Mike H
external usenet poster
 
Posts: 11,504
Default 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

  #6  
Old November 8th 07, 03:48 PM posted to microsoft.public.excel.misc
Bernie Deitrick
external usenet poster
 
Posts: 5,444
Default 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



  #7  
Old November 8th 07, 03:51 PM posted to microsoft.public.excel.misc
Sloth
external usenet poster
 
Posts: 252
Default 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

  #8  
Old November 8th 07, 03:52 PM posted to microsoft.public.excel.misc
David Biddulph[_2_]
external usenet poster
 
Posts: 8,651
Default 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



  #9  
Old November 8th 07, 04:51 PM posted to microsoft.public.excel.misc
Gary''s Student
external usenet poster
 
Posts: 11,059
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2002 : Convert Positive Numbers to Negative Numbers ? Mr. Low Excel Discussion (Misc queries) 2 November 6th 06 03:30 PM
How to switch negative numbers to positive numbers in Excel ? amashmallow888 Excel Worksheet Functions 2 September 25th 06 06:34 AM
change 2000 cells (negative numbers) into positive numbers lisbern Excel Worksheet Functions 2 August 16th 06 05:54 PM
convert negative numbers to positive numbers and vice versa bill gras Excel Worksheet Functions 4 December 7th 05 01:39 AM
How to change a series of positive numbers to negative numbers Ellie Excel Worksheet Functions 5 September 5th 05 05:10 PM


All times are GMT +1. The time now is 11:29 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.