Change Cells of Certain Value to Blank (2000)

Topic

New Reply

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

Replies

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

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

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