vba code to search and clear contents

[londa_vba]

vba code to search column B for the words "Thermal Mixer" and when found to clear the contents (not delete) of that row. The row where "Thermal Mixer" is found will vary and there may be multiple instances on the sheet. Main point is to keep the row count the same.

from this

23-0001	706	8.1	Angela Sanders	~1/2 swab (2)	100
23-0002	811	2.1	Carl Cook	~1/2 swab (2)	100
23-0003		RB1	Reagent Blank 1		100
	Thermal Mixer	Temp In:		00:00
	Thermal Mixer	Temp In:		00:00

23-0004	901	8.1	Miles Davis	~1/2 swab (2)	100
23-0005	602	2.1	Sarah Anderson	~1/2 swab (2)	100
23-0006		RB1	Reagent Blank 1		100
	Thermal Mixer	Temp In:		00:00
	Thermal Mixer	Temp In:		00:00

to this

23-0001	706	8.1	Angela Sanders	~1/2 swab (2)	100
23-0002	811	2.1	Carl Cook	~1/2 swab (2)	100
23-0003		RB1	Reagent Blank 1		100

23-0004	901	8.1	Miles Davis	~1/2 swab (2)	100
23-0005	602	2.1	Sarah Anderson	~1/2 swab (2)	100
23-0006		RB1	Reagent Blank 1		100

thank you for any suggestions

 

[hrayani]

Hello,

Try below code

Sub ForEachCell()
    Dim Cell As Range
    
    For Each Cell In Sheets("Sheet1").Range("A1:F12")
        If Cell.Value = "Thermal Mixer" Then Cell.EntireRow.ClearContents
    Next Cell
    
End Sub

 

[londa_vba]

Hello,

Try below code

Sub ForEachCell()
    Dim Cell As Range
   
    For Each Cell In Sheets("Sheet1").Range("A1:F12")
        If Cell.Value = "Thermal Mixer" Then Cell.EntireRow.ClearContents
    Next Cell
   
End Sub

Thank you for the response but this is not working for me. I even changed "Sheet1" to my actual sheet name and nothing

 

[DanteAmor]

Hi @londa_vba . I hope you are well.

Try this.
I assume you have header, so change the 1 in this line if the header is in another row.
ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.ClearContents

Sub Macro2()
  ActiveSheet.Range("A1:B" & Range("B" & Rows.Count).End(3).Row).AutoFilter 2, "=*Thermal Mixer*"
  ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.ClearContents
  Range("A1").AutoFilter
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[londa_vba]

Hi @londa_vba . I hope you are well.

Try this.
I assume you have header, so change the 1 in this line if the header is in another row.
ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.ClearContents

Sub Macro2()
  ActiveSheet.Range("A1:B" & Range("B" & Rows.Count).End(3).Row).AutoFilter 2, "=*Thermal Mixer*"
  ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.ClearContents
  Range("A1").AutoFilter
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Yes that was the issue. I had a header in row 1 and 2 so setting offset to 2 worked for me. Thank you