How to: Remove all negative numbers

Last week I received the following question:
Is there an ASAP Utilities function that can remove all negative numbers from a spreadsheet? I haven't found it yet.

Yes there is. You can use ASAP Utilities to help you to quickly delete negative numbers.

You can use the following tool to select all cells with negative numbers (values less then zero).
Then you can press the DEL key to have these values removed:
ASAP Utilities » Select » Select cells based on content, formatting and more...

Select the negative numbers

17 Responses to “How to: Remove all negative numbers”

  1. Hi Bastien,

    May the couple Live 1000 years !

    Whenever we use the utility "Remove leading, trailing and excessive spaces" for the first time on any worksheet, it given an error message something 'object does not exist" (I have sent a snapshot through separate email to you), but after clicking Ok when we use it 2nd time, it works correctly…. Seems to be a bug.

    Regards
    CA Kanwaljit Singh Dhunna

  2. Hello Kanwaljit,

    Thank you for your feedback. I have sent you an email.
    Unfortunately I cannot reproduce this error.
    Do you get this error all the time or is it with a specific workbook?
    Can you perhaps send me an example workbook that I can use to reproduce the problem with?

    Kind regards,
    Bastien

  3. Hi Bastien,

    May the couple Live 1000 years !

    Not all the time. I too am trying to replicate it. Just seems to forget the worksheet where it occured. Will get back to you asap.

    Regards
    CA Kanwaljit Singh Dhunna

  4. Hi Bastien,

    May the couple Live 1000 years !

    It seems the error is appearing whenever I use the utility for the FIRST time after starting the system. I am lost………………..

    Regards
    CA Kanwaljit Singh Dhunna

  5. Hi Bastien,

    May the couple Live 1000 years !

    It seems the error is appearing whenever I use the utility for the FIRST time after starting the system. I am lost………………..as it again appeared on a worksheet. I closed the file without saving the changes. But after reopening the unsaved workbook, when I used the utility on the same worksheet, it worked fine….. It is happening, though I am not able to identify the situation precisely….

    Regards
    CA Kanwaljit Singh Dhunna

  6. Hello Kanwaljit,

    I'm sorry but I can't reproduce that and I have never seen this reported before.
    You can try to reinstall ASAP Utilities (just run the setup again) and see if that solves the problem.
    However, weird problems such as these can often be solved by doing a repair of your Office installation:
    1. Close Excel if it is open.
    2. In the Control Panel choose "Add or Remove Programs" (or Programs and Features in Windows Vista).
    3. Select Microsoft Office in the list and click on the Change button.
    4. Choose "Repair"
    Can you tell me if that solves the problem?

    Kind regards,
    Bastien

  7. Hi Bastien,

    May the couple Live 1000 years !

    Till I try yours solutions, Just let me mention what precisely is happening. Whenever I use the utility for the FIRST time after starting the Excel (not after starting the system as I said earlier), the above error is occurring. It is happening each and every time I restarts Excel and use the Utility for the First Time. After that everything seems to work fine. And it is happening with each and every worksheet and not just some particular sheet. Also as an experiment, I started one instance of Excel and used the utility for the first time. Same error occurred and on 2nd use onwards everything was fine on all the sheets (i.e., other than the original sheet which gave error message). Then I opened 2nd instance of Excel without closing the 1st instance and same problem occurred. Similar is the case with Excel 2007. I will get back to you after trying yours solutions. But it is happening exactly like this.

    Regards
    CA Kanwaljit Singh Dhunna

  8. Hi Bastien,

    May the couple Live 1000 years !

    Just downloaded and installed the new version. It is fine now. Thanks a lot and sorry for the unnecessary botheration.

    Regards
    CA Kanwaljit Singh Dhunna

  9. Hello Kanwaljit,

    I'm glad to read the problem is fixed.

    Kind regards,
    Bastien

  10. Hi Bastien,

    May the couple Live 1000 years !

    The issue has resurfaced in the new version too. I have sent you an email with files and screenshot. And strangely enough the error is happening on the 1st use of utility only and that too on any file.

    Regards
    CA Kanwaljit Singh Dhunna

  11. Hello Kanwaljit,

    Have you received my email I sent earlier?

    I am still unable to reproduce the problem and I have never ever heard it before from other users.
    Do you have colleagues that get the same error too?

    Can you do a repair of you Office installation?
    I think that should solve the problem because that often solves weird errors such as these.
    1. Close Excel if it is open.
    2. In the Control Panel choose "Add or Remove Programs” (or Programs and Features in Windows Vista).
    3. Select Microsoft Office in the list and click on the Change button.
    4. Choose "Repair”
    Please let me know if that solves the problem.

    Kind regards,
    Bastien Mensink

  12. Hi Bastien,

    May the couple Live 1000 years !

    May appear strange, but it seems the Utlitiy "Remove Unused Empty ending rows/columns" has stopped working on Worksheets containing Pivot Tables. I have tested it on two systems, with same results.

    Regards
    CA Kanwaljit Singh Dhunna
    India

  13. Hello Kanwaljit,

    I'm sorry but I'm still unable to reproduce the problem, even with pivot tables.
    I have tested it with pivot tables before and now again on a machine with Excel 2002/XP one with Excel 2003 and one with Excel 2007. All the times the utility works as expected. Furthermore I still haven't heard anyone with the same problem which is why I think that it is something specific to your computer/configuration.
    Do you have other add-ins installed on the computer(s)? You can create a list with the following tool:
    ASAP Utilities » System » Create a list of all add-ins
    http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=161&utilities=147&lang=en_us

    Kind regards,
    Bastien Mensink

  14. Hi Bastien,

    May the couple Live 1000 years !

    I used "Detect and Repair" utility on the "Help" menu. The Utlitiy "Remove Unused Empty ending rows/columns” is not working on Sheet on Which Pivot Table is active or on which I have removed the Pivot Tables by Pasting only the values. I have sent you such a sheet. Strangely it is working fine on other sheets. As an experiment I used the Utility on a sheets by inserting some rows which have a continuous Serial number, but removed the serial number meaning that there are only blank rows with some formatting but no data at all. But the utility was unable to delete those rows. Then I pasted the formatting from Virgin rows and used the utility and it worked fine. Still a mystery to me. The Add-in installed are installed since long and never caused such a behaviour in the past. I will post further feedback when I notice something.

    Regards
    CA Kanwaljit Singh Dhunna
    India

  15. Hi Bastien,

    May the couple Live 1000 years !

    I am here to bother you again. I have tried all yours suggestions regarding repairing Excel / Office. But the following utilities are still not working properly.
    Sheets- > 22. Remove Unused Empty ending rows/columns” (Not working on a sheet containing Pivot Tables)
    Sheets- > 23. Reset Excel's Last Cell" (Not working on any sheet)

    Strangely enough there is another Addin ExTools having a utility comparable to "Reset Excel's Last Cell" utility (See ExTools (http://www.excel-extools.com/. Use Worksheet->Reset Last Cell) is working fine on the same sheet.

    Other utilities seems to be working fine BTW.

    Regards
    CA Kanwaljit Singh Dhunna
    India

  16. Hi Bastien,

    May the couple Live 1000 years !

    BTW I have also completely uninstalled and reinstalled the ASAP too.

    Regards
    CA Kanwaljit Singh Dhunna
    India

  17. Hello Kanwaljit,

    It is a difficult and weird problem. I cannot reproduce it and it hasn't been reported by anyone else. Furthermore it works fine on your home computer.
    Can you install the latest version of ASAP Utilties, it has just been released:
    http://www.asap-utilities.com/download-asap-utilities.php
    Can you send me the error messages you get when you use these two utilities?
    Can you also send me a list of all the other add-ins you have installed? I have seen in the past that another add-in causes problems in ASAP Utilities.
    http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=161&utilities=147&lang=en_us

    Thank you,

    Kind regards,
    Bastien Mensink

Pinterest