• Change Cells of Certain Value to Blank (2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Change Cells of Certain Value to Blank (2000)

    Author
    Topic
    WSjcrawfor
    AskWoody Lounger
    February 22, 2002 at 6:17 pm #367232

    I have a user who has a large spreadsheet that will be sent to the state for further analysis, but before he sends it, all cells of a certain value, for example 1,000, need to be converted to blanks. Not spaces or zero. I’ve looked at a couple of options, but feel I am probably missing a simple conditional statement of some kind.

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSAndrew Cronnolly
      AskWoody Lounger
      February 22, 2002 at 6:23 pm #571964

      How to deal with this depends how the initial value of 1,000 is arrived at. Is it from th eresult of a formula or direct input ?

      If from a formula ypu may need to amend the formula to return blank if otherwis eit would return 1000. For example if the formula summed A1 and B1, you could use 

          =IF(A1+B1=1000,"",A1+B1)

      Andrew C

      Reply | Quote
      • WSjcrawfor
        AskWoody Lounger
        February 22, 2002 at 7:01 pm #571971

        Andrew, here’s some more details. I talked to the user again. The figure is NOT part of a formula, but comes from a data collection system so it’s just a list of data by date and time. The state wants every time that a value reports in at greater than .79 for the cell to become blank or null. Glad for your help on this one.

        Reply | Quote
        • WSAndrew Cronnolly
          AskWoody Lounger
          February 22, 2002 at 7:17 pm #571973

          The simples way is probably with a samll macro as follows :

              Sub ClearValues()
    Dim oCell As Range
    For Each oCell In Selection
        If oCell.Value > 0.79 Then
            oCell.Value = ""
        End If
    Next
    End Sub

          Hope that helps your user.

          Andrew C

          Reply | Quote
    Viewing 0 reply threads
    Reply To: Change Cells of Certain Value to Blank (2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.