Find text and clear contents of another cell

[Migaspt]

Hello guys,

I'm trying to create a code that has to be run manually.

It should search for the word "total" in column A and clear the contents of the cells in columns K and L in the same row.

I tried searching and using bits of others codes I saw here but I can't seem to find anything useful.

Thanks in advance!

 

[Momentman]

This should work

Sub ClearColumns()
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("A" & i).Value = "total" Then
            Range("K" & i & ":L" & i).Value = ""
        End If
    Next i
End Sub

or

Sub ClearColumns2()
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If InStr(1, Range("A" & i).Value, "total") > 0 Then
            Range("K" & i & ":L" & i).Value = ""
        End If
    Next i
End Sub

or this

Sub ClearColumnsKL()
    With Range(Cells(1, 1), Cells(Range("A" & Rows.Count).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
        .AutoFilter Field:=1, Criteria1:="total"
        Range("K:L").SpecialCells(xlCellTypeVisible).ClearContents
        .AutoFilter
    End With
End Sub

 

[Trevor G]

Try this:

Sub FindTotalClear()
Range("A1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1).Select
If ActiveCell = "Total" Then
ActiveCell.Offset(0, 10).ClearContents
ActiveCell.Offset(0, 11).ClearContents
End If
Loop
End Sub

 

[Migaspt]

This should work

Sub ClearColumns()
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("A" & i).Value = "total" Then
            Range("K" & i & ":L" & i).Value = ""
        End If
    Next i
End Sub

or

Sub ClearColumns2()
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If InStr(1, Range("A" & i).Value, "total") > 0 Then
            Range("K" & i & ":L" & i).Value = ""
        End If
    Next i
End Sub

or this

Sub ClearColumnsKL()
    With Range(Cells(1, 1), Cells(Range("A" & Rows.Count).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
        .AutoFilter Field:=1, Criteria1:="total"
        Range("K:L").SpecialCells(xlCellTypeVisible).ClearContents
        .AutoFilter
    End With
End Sub

Thanks Momentman!

I tried running all of them, the first 2 did not clear any cell.

The last one clears the cells but also the cells of the line with the titles, can we add something for it to clear the contents of the cells starting on line 9 only?

 

[Migaspt]

ADVERTISEMENT

Try this:

Thank you Trevor! This was what I was hoping to get, it finds the cells with "Total" as an exact match, can we add something so it finds the word total and not the entire cell content?

EDIT: Or if it is possible to add the formula =RIGHT(ActiveCell,5) = "total" or something like this it would search for what I need

 

[Momentman]

Thanks Momentman!

I tried running all of them, the first 2 did not clear any cell.

The last one clears the cells but also the cells of the line with the titles, can we add something for it to clear the contents of the cells starting on line 9 only?

Well, am not sure why that is, except you didn't explain your request properly

Is total the only word in the cell? is it "Total" or "total"?

The 3 macros work for me as expected anyways

You could use

Sub ClearColumns2()
    lastrow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    For i = 9 To lastrow
        If InStr(1, Range("A" & i).Value, "Total") > 0 Then
            Range("K" & i & ":L" & i).Value = ""
        End If
    Next i
End Sub

 

[Migaspt]

ADVERTISEMENT

Well, am not sure why that is, except you didn't explain your request properly

Is total the only word in the cell? is it "Total" or "total"?

The 3 macros work for me as expected anyways

I'm sorry. I might have not said everything.

The word I want to search for is "Total", it is not the entire cell content.

 

[Momentman]

I'm sorry. I might have not said everything.

The word I want to search for is "Total", it is not the entire cell content.

You can check the Last macro i posted and see if it works for you

 

[Migaspt]

Thanks Momentman, this one does clear the cells that I need:

[COLOR=#333333]Sub ClearColumns2()[/COLOR]    lastrow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    For i = 9 To lastrow
        If InStr(1, Range("A" & i).Value, "Total") > 0 Then
            Range("K" & i & ":L" & i).Value = ""
        End If
    Next i 
[FONT=Verdana]End Sub[/FONT][COLOR=#222222][FONT=Verdana]

[/FONT][/COLOR]

 

[Migaspt]

Thank you Trevor! This was what I was hoping to get, it finds the cells with "Total" as an exact match, can we add something so it finds the word total and not the entire cell content?

EDIT: Or if it is possible to add the formula =RIGHT(ActiveCell,5) = "total" or something like this it would search for what I need

By the way, I also found that this one works:

Sub FindTotalClear()Range("A9").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1).Select
If Right(ActiveCell, 5) = "Total" Then
ActiveCell.Offset(0, 10).ClearContents
ActiveCell.Offset(0, 11).ClearContents
End If
Loop
End Sub