Clear data based on cell

[bobaol]

Hello, i have a few thousand cells in column AA containing the word BlankClearMe. I want to clear A to BZ when the word is found. For example, cell AA14=BlankClearMe, then clear A14 to BZ14. or when AA2397=BlankClearMe, then clear A2397 to BZ2397. any help is appreciated. Thanks in advance.

 

[Joe4]

Try this:

Sub MyClearCells()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column AA with data
    lr = Cells(Rows.Count, "AA").End(xlUp).Row
   
'   Loop through all rows starting from row 1
    For r = 1 to lr
'       Check to see if value in column AA is "BlankClearMe"
        If Cells(r, "AA") = "BlankClearMe" Then
'           Clear cells
            Range(Cells(r, "A"), Cells(r, "BZ")).ClearContents
        End If
    Next r
   
    Application.ScreenUpdating = True
   
    Exit Sub
   
End Sub

 

[MARK858]

Just another option to test if using 2010 or newer

Sub FilterClear()
    Application.ScreenUpdating = False
   
    With Range("AA1:AA" & Range("AA" & Rows.Count).End(xlUp).Row)
   
        .AutoFilter 1, "BlankClearMe"
       
        On Error Resume Next
       .Offset(1, -26).Resize(.Rows.Count - 1, 78).SpecialCells(xlCellTypeVisible).ClearContents
        On Error GoTo 0
        ActiveSheet.ShowAllData
   
    End With
   
    Application.ScreenUpdating = True
End Sub

 

[bobaol]

Thank you both Joe4 and Mark858. Both worked well. - bobaol44484

 

[Joe4]

You are welcome.
Glad we were able to help!