Clear all cells that contain a 1 or 0

[JadonR]

I would like to use a macro to clear (not delete) all cells in column AA that contain a 1 or 0. I've sorted through various threads, but can't seem to find one that will work for me. Can someone point me in the right direction?

 

[Desu Nota from Columbus]

Sub ClearAA()
   Dim r As Long
   Dim lrow As Long

  Application.ScreenUpdating = False

  lrow = Range("AA" & Rows.Count).End(xlUp).Row

  For r = lrow To 1 Step -1

     If Cells(r, 27).Value = 0 Or Cells(r, 27).Value = 1 Then
       Cells(r, 27).ClearContents
     End If
     Next r

     Application.ScreenUpdating = True

End Sub

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("AA" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("AA" & i).Value = 0 Or Range("AA" & i).Value = 1 Then Range("AA" & i).ClearContents
Next i
End Sub

 

[CharlesChuckieCharles]

or try this which works on an area which you select

Public Sub Clear10()
Dim CC As Range

For Each CC In Selection
    
    If CC.Value = 1 Or CC.Value = 0 Then
       CC.ClearContents
    End If
 
Next CC

End Sub

 

[JadonR]

Wow, thanks guys - they all work although I prefer the first two as they don't require a selection. I tested it in a spreadsheet with around 5000 rows and it seems to take quite a bit of time. I will have as many as 20,000 rows sometimes. Any alternative to doing this that may be faster. Maybe I could create an additional column and use a vlookup (if it matches a 1 or 0 then "")?

 

[Jonmo1]

What else other than 1 or 0 might appear in column AA ?

 

[JadonR]

What else other than 1 or 0 might appear in column AA ?

Many different numbers, everything from a 2 to 500 and everything in between...that's the problem. So maybe a vlookup wouldn't be ideal.

 

[Jonmo1]

Many different numbers, everything from a 2 to 500 and everything in between...that's the problem. So maybe a vlookup wouldn't be ideal.

OK, what I was thinking was if only 1 or 0 or blank or text was possible, then you could use specialcells(numbers)..

But that won't work...

Common method to improve speed is to turn off Events/Screenupdating/Calculation.

here's Vog's code, with those features added.

Sub test()
Dim LR As Long, i As Long, PrevCalc As Variant
 
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    PrevCalc = .Calculation
    .Calculation = xlCalculationManual
End With

LR = Range("AA" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("AA" & i).Value = 0 Or Range("AA" & i).Value = 1 Then Range("AA" & i).ClearContents
Next i
 
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .Calculation = PrevCalc
End With

End Sub

 

[JadonR]

Wow, that works perfectly! Super fast. So I know what Screenupdating is, but what are the Events and Calculation?

 

[Jonmo1]

Wow, that works perfectly! Super fast. So I know what Screenupdating is, but what are the Events and Calculation?

Glad to help, thanks for the feedback..

Anytime a cell value is changed, if a formula refers to that cell then it is recalculated.
When cells are cleared, that will likely be triggering formulas to recalculate, increasing the time required to complete the macro.
So we turn off calculation (set to xlcalculationmanual)
run the code
Turn it back on at the end.


Events are similar to calculation, but for VBA code.
There are event codes for when a cell changes, or calculates,
or when you select a cell, or double click a cell, or right click a cell.

A code can be written to run whenever one of those events occurs.
That can also increase the time to complete the macro.

Since you didn't know what events are, it's likely you're not using them, so that part can probably be omitted.