Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Change Cells of Certain Value to Blank (2000)

    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.
    Thanks much, Judy Crawford

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Change Cells of Certain Value to Blank (2000)

    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 <pre> =IF(A1+B1=1000,"",A1+B1)</pre>



    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    192
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Change Cells of Certain Value to Blank (2000)

    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.
    Thanks much, Judy Crawford

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Change Cells of Certain Value to Blank (2000)

    The simples way is probably with a samll macro as follows :<pre> Sub ClearValues()
    Dim oCell As Range
    For Each oCell In Selection
    If oCell.Value > 0.79 Then
    oCell.Value = ""
    End If
    Next
    End Sub</pre>

    Hope that helps your user.

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •