VBA Clear Cells Issue

Question

Hello All,

I have some VBA that does not cause any debug issues so looks good but it does not clear the cells as expected, the first part of the VBA works fine. The reason for the long winded way of clearing cells is that the odd row needs to be cleared but the even rows need to be retained.

I wonder if you could look at my code and tell me what is wrong, as follows

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Application.EnableEvents = False
If Sh.Range("BE2").Value <> 1 And Sh.Range("BE5").Value = 10 Then
Sh.Range("BA10:BB68").Copy
Sh.Range("BA10").PasteSpecial Paste:=xlPasteValues
Sh.Range("BE2").Value = 1
Application.EnableEvents = True
End If
If Sh.Range("AF6").Value < 1 Then
Range("O9").Select
ActiveCell.FormulaR1C1 = ""
Range("O11").Select
ActiveCell.FormulaR1C1 = ""
Range("O13").Select
ActiveCell.FormulaR1C1 = ""
Range("O15").Select
ActiveCell.FormulaR1C1 = ""
Range("O17").Select
ActiveCell.FormulaR1C1 = ""
Range("O19").Select
ActiveCell.FormulaR1C1 = ""
Range("O21").Select
ActiveCell.FormulaR1C1 = ""
Range("O23").Select
ActiveCell.FormulaR1C1 = ""
Range("O25").Select
ActiveCell.FormulaR1C1 = ""
Range("O27").Select
ActiveCell.FormulaR1C1 = ""
Range("O29").Select
ActiveCell.FormulaR1C1 = ""
Range("O31").Select
ActiveCell.FormulaR1C1 = ""
Range("O33").Select
ActiveCell.FormulaR1C1 = ""
Range("O35").Select
ActiveCell.FormulaR1C1 = ""
Range("O37").Select
ActiveCell.FormulaR1C1 = ""
Range("O39").Select
ActiveCell.FormulaR1C1 = ""
Range("O41").Select
ActiveCell.FormulaR1C1 = ""
Range("O43").Select
ActiveCell.FormulaR1C1 = ""
Range("O45").Select
ActiveCell.FormulaR1C1 = ""
Range("O47").Select
ActiveCell.FormulaR1C1 = ""
Range("O49").Select
ActiveCell.FormulaR1C1 = ""
Range("O51").Select
ActiveCell.FormulaR1C1 = ""
Range("O53").Select
ActiveCell.FormulaR1C1 = ""
Range("O55").Select
ActiveCell.FormulaR1C1 = ""
Range("O57").Select
ActiveCell.FormulaR1C1 = ""
Range("O59").Select
ActiveCell.FormulaR1C1 = ""
Range("O61").Select
ActiveCell.FormulaR1C1 = ""
Range("O63").Select
ActiveCell.FormulaR1C1 = ""
Range("O65").Select
ActiveCell.FormulaR1C1 = ""
Range("O67").Select
ActiveCell.FormulaR1C1 = ""
Range("BB1").Select
End If
End Sub

Thanks

Jonathan

Answer 1

In place of all of your code selecting cells and using the ActiveCell, try

Dim r As Long
If Sh.Range("AF6").Value < 1 Then
With Sh.Range("O1")
For r = 8 To 66 Step 2
.Offset(r, 0).Value = ""
Next r
End With
End If