• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete entire row if contains certain text.

P

premjeetgupta

Member
Hi All,

I have multiple excel sheet where in specific column "A" contains certain text like "OPOCH" & "VA" i want to delete all sheet entire row that contains this word.
 
Hi,
Try this:
Code: 
Sub DeleteRows()
Dim c As Range
Dim SrchRng As Range
Dim SrchStr As String

Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A1000").End(xlUp))
SrchStr = InputBox("Please Enter A Search String")
Do
Set c = SrchRng.Find(SrchStr, LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing

End Sub

Copy from:

Regards,
 
Hi Khalid,
I haven't checked with all the sheets. it is working for only active sheet. Is there any modification possible for all the sheets.
 
Hi Gupta,
Above code is for active sheet only, you have to run individually sheet by sheet.

Tomorrow, I will try to modify it "for each sheet", give me some time as now i have to go home :)

I will appreciate if any member update it.
 
Hi,

Try below code:

Code: 
Option Explicit

Sub DeleteRows()
Dim c As Range
Dim SrchRng As Range
Dim SrchStr As String
Dim ws As Worksheet

SrchStr = InputBox("Please Enter A Search String")

For Each ws In ThisWorkbook.Worksheets
Set SrchRng = ws.Range("A1", ws.Range("A1000").End(xlUp))

Do
Set c = SrchRng.Find(SrchStr, LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
Next
End Sub

Regards,
 
You must log in or register to reply here.
Back
Top