Macro - Clear cell contents based on other cell

Simple one for most on here but any help will be greatly appreciated.

I need a macro that will look at a range (AX5:AX2000), if a cell in that range = 1 then it will delete the corresponding cell on that row in columns Q:S.

Thanks in advance!

  Sub clear()

        Application.ScreenUpdating = 0
        LR = Cells.Find("*", , , , xlByRows, xlPrevious).Row
        
        For i = 5 To LR
            If Cells(i, "AX") = 1 Then Cells(i, "Q").Resize(, 3).ClearContents
        
        Next i
        
        Application.ScreenUpdating = True
End Sub

Thanks guys. They both work but is there any way to make them quicker?

Of the 2000 or so rows there will be anywhere between 20% and 90% that will need to be cleared so this is taking quite a long time.

Try this one

Sub multiple()
Dim x, i&, LR&
   Application.ScreenUpdating = 0
 On Error Resume Next
 LR = Cells.Find("*", , , , xlByRows, xlPrevious).Row
        
 For i = LR To 5 Step -1
    If Cells(i, "AX") = 1 Then
        If myrng1 Is Nothing Then
                Set myrng1 = Range(Cells(i, "Q"), Cells(i, "S"))
            Else
                Set myrng1 = Union(myrng1, Range(Cells(i, "Q"), Cells(i, "S")))
            End If
        End If
    Next i
        If Not myrng1 Is Nothing Then
            myrng1.ClearContents
        End If
          Application.ScreenUpdating = True
End Sub

Originally Posted by AB33

Try this one

Sub multiple()
Dim x, i&, LR&
   Application.ScreenUpdating = 0
 On Error Resume Next
 LR = Cells.Find("*", , , , xlByRows, xlPrevious).Row
        
 For i = LR To 5 Step -1
    If Cells(i, "AX") = 1 Then
        If myrng1 Is Nothing Then
                Set myrng1 = Range(Cells(i, "Q"), Cells(i, "S"))
            Else
                Set myrng1 = Union(myrng1, Range(Cells(i, "Q"), Cells(i, "S")))
            End If
        End If
    Next i
        If Not myrng1 Is Nothing Then
            myrng1.ClearContents
        End If
          Application.ScreenUpdating = True
End Sub

Wow, what a hero! Thanks

97hills,
You are welcome!

Could you please now close (Mark) this thread as solved? Go in to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.

Originally Posted by AB33

97hills,
You are welcome!

Could you please now close (Mark) this thread as solved? Go in to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.

I'll admit I'm a complete novice when it comes to VBA but I'm usually able to figure it out once I can see the code. This one I don't have a clue how it works! I was hoping to be able to edit it and use it for another purpose but can't figure out what you've done.

It's pretty similar but instead of clearing whatever is in cells Q:S I just need the value "X" to be written in Q only.

Again, thanks and I'll make sure I mark the thread as solved

97 hills,
Please do not reply with quote- It has not purpose other than to clutter the space of this page.
Back to the slow code.

  Sub clear()

        Application.ScreenUpdating = 0
        LR = Cells.Find("*", , , , xlByRows, xlPrevious).Row
        
        For i = 5 To LR
            If Cells(i, "AX") = 1 Then Cells(i, "Q") = "x"
        
        Next i
        
        Application.ScreenUpdating = True
End Sub

Great, thanks