How to Delete Rows Based on Given text

[badral9]

Hello,

I'm new to VBA and macro. I want to delete rows if given text contains in the row. I googled and did some research and created macros with bunch of google result :)


Sub RunforColumn1()
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
xSh.Select
Call dontrun1
Next
Application.ScreenUpdating = True
End Sub

Sub dontrun1()
Dim ws As Worksheet
Set ws = ActiveSheet
'clear existing filters
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
'filter range where column 1
ws.Range("A1:I50000").AutoFilter field:=1, Criteria1:="random text"
'delete rows that are visible
Application.DisplayAlerts = False
ws.Range("A2:I50000").SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
'remove filter
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
End Sub

Above macro works only one column at the time. Now i have multiple same macros on every column. Text i want to delete is can be any column. and i want to do it in single macro.

Any suggestion helpful to me :)

[snb]

Adapt the searchstring "'snb".


Sub M_snb()
on error resume next
for each it in sheets
do
it.cells.find("snb")="=1/0"
loop until err.number <>0
it.cells(2,16).entirerow.delete
err.clear
next
End Sub

[badral9]

Adapt the searchstring "'snb".


Sub M_snb()
on error resume next
for each it in sheets
do
it.cells.find("snb")="=1/0"
loop until err.number <>0
it.cells(2,16).entirerow.delete
err.clear
next
End Sub
thank your for your reply
this code find text and replace it with =1/0 formula, not deleting row
do you know why

[snb]

Yes, I do.


Sub M_snb()
on error resume next
for each it in sheets
do
it.cells.find("snb")="=1/0"
loop until err.number <>0
it.cells(-4123,16).entirerow.delete
err.clear
next
End Sub