VBA Clear Cells Issue

Jonathan Challinor 96 Reputation points
2021-11-24T18:38:26.693+00:00

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

{count} votes

1 answer

Sort by: Most helpful
  1. 2021-12-05T09:26:42.88+00:00

    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

    No comments