Clear all cells when the are not between 0.90-0.95

[FvdF]

I need a formula or vba script that will clear all cells in column "C,E,G,I" that are not between 0.90-0.95.

Who can help me with this?

Kind Regards,

FvdF

[Sam Capricci]

formulas can't clear cells, you could write a formula in another area that would indicate which cells to clear or a formula that returns a blank if the cell is not between those parameters and the numbers that are within those parameters, then copy over those values and paste them into the same cells. Is that something you can work with? otherwise you will need VBA.

this would be the formula version... =IF(OR(C3<0.9,C3>0.95),"",C3) then copy and paste special the results over the column C, repeat for E, G and I.

[wk9128]

pls try this code

Sub wkClearCells()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim cellsToClear As Range
    Set ws = ThisWorkbook.Worksheets("Blad1")
    Set rng = ws.Range("C3:C11,E3:E11,G3:G11,I3:I11")
    For Each cell In rng
        If Not (cell.Value >= 0.9 And cell.Value <= 0.95) Then
            If cellsToClear Is Nothing Then
                Set cellsToClear = cell
            Else
                Set cellsToClear = Union(cellsToClear, cell)
            End If
        End If
    Next cell
    If Not cellsToClear Is Nothing Then
        cellsToClear.ClearContents
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

[FvdF]

Hoi wk9128

This works fine as I can see now. Still one question can you change this range with rows C,E,F,I
These rows are feeding every day with new cells.

Kind regards
FvdF

[Sam Capricci]

well I did not write the code so the credit goes to wk9128 but I would assume you'd make the change here...

Sub wkClearCells()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim cellsToClear As Range
    Set ws = ThisWorkbook.Worksheets("Blad1")
    Set rng = ws.Range("C3:C11,E3:E11,F3:F11,I3:I11")
    For Each cell In rng
        If Not (cell.Value >= 0.9 And cell.Value <= 0.95) Then
            If cellsToClear Is Nothing Then
                Set cellsToClear = cell
            Else
                Set cellsToClear = Union(cellsToClear, cell)
            End If
        End If
    Next cell
    If Not cellsToClear Is Nothing Then
        cellsToClear.ClearContents
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub 

[Sam Capricci]

note to admin/moderators, I tried to use html settings but it wouldn't show properly - sorry.

[leelnich]

Or this. You can adjust columns in the red line:

Sub FilterVals01()
Dim i As Long, k as Long, COLS As Variant

Sheets("Blad1").Activate
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

COLS = Split("C,E,G,I", ",")

With Cells(1, 1).CurrentRegion.Offset(1)
  For i = 0 To UBound(COLS)
    k = Columns(COLS(i)).Column
    .AutoFilter k, "<.90", xlOr, ">.95"
    .Offset(1).Columns(k).SpecialCells(xlVisible).ClearContents
    .AutoFilter k
  Next
End With

ActiveSheet.AutoFilterMode = False

End Sub