Sorting and removal of data

[AnnaKarin]

Hi!
I have a big spreadsheet with data. For each column I need to remove cells with numbers that are below 0 and above 2. As I do it now I use filters to find the numbers easily and delete them manually, but there must be some more efficient way? And there are a lot of columns to go through...

 

[wigi]

What columns do you need this to happen?

Are these the interger values 0, 1, 2 or also values in between?

Do you need to clear the cells or delete the row?

Do the cells contain formulas or hard values?

 

[AnnaKarin]

I need it to happen for all columns.
I need to save all values between 0-2.
The cells should be deleted, not the column.
The columns only contain hard values, no formulas.

 

[wigi]

Then, consider this code:

Dim rng As Range
For Each rng In ActiveSheet.UsedRange
   If rng.Value >= 0 And rng.Value <= 2 Then rng.ClearContents
Next

Depending on what else happens in your sheet, restricting the UsedRange using SpecialCells might speeding up macro executing.

 

[wigi]

A possibly much faster approach would be:

Sub ff()

    Dim rng As Range
    
    With ActiveSheet.UsedRange
    
        sq = .Value
    
        For i = 1 To UBound(sq, 1)
            For j = 1 To UBound(sq, 2)
                If sq(i, j) >= 0 And sq(i, j) <= 2 Then sq(i, j) = ""
            Next
        Next
    
        .Value = sq
        
    End With

End Sub

, or variants of this code.

 

[AnnaKarin]

Thank you for your answers! Unfortunately I'm a bit of a beginner at this so I have to read up on how to make macros But you got to learn sometime right

 

[AnnaKarin]

It works!!
Although the
">= 0 And rng.Value <= 2"
should be
"<= 0 And rng.Value >= 2"

Thank you so much for your help!

 

[wigi]

It works!!
Although the
">= 0 And rng.Value <= 2"
should be
"<= 0 And rng.Value >= 2"

True, good catch.