How can I delete rows with specific data found in column A using a Macro in Excel?

What commands in a macro can I use to delete specific data it finds in Column A?

For instance if the following words are found, how can I delete the entire row?


'Run
100questionsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Try this

Sub DeleteSpecific
Dim cCell as Range

set Cell = Activesheet.Range("A:A").Find(What:="Run",Lookin:=XlValues,Lookat:=xlWhole,MatchCase:=False)
if not cCell is Nothing then
     Activesheet.range(cCell.Row,"A").EntireRow.Delete
Endif

EndSub

Open in new window


BTW is it Run or 'Run ??? if latter then replace this line
set Cell = Activesheet.Range("A:A").Find(What:="Run",Lookin:=XlValues,Lookat:=xlWhole,MatchCase:=False)

by this line
set Cell = Activesheet.Range("A:A").Find(What:="'Run",Lookin:=XlValues,Lookat:=xlWhole,MatchCase:=False)

gowflow
0
100questionsAuthor Commented:
This does not work.
Compile Error:  
Sub or Function not defined.
0
gowflowCommented:
Ooops typo !!!

Sub DeleteSpecific()
Dim cCell As Range

Set cCell = ActiveSheet.Range("A:A").Find(What:="Run", LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
If Not cCell Is Nothing Then
     ActiveSheet.Range("A" & cCell.Row).EntireRow.Delete
End If

End Sub

Open in new window


gowflow
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Hakan YılmazTechnical Office MEP EngineerCommented:
You can change it with this
cCell.EntireRow.Delete

Open in new window

0
gowflowCommented:
And if there are many instances of Run in Column A this code will take care of deleting all rows that contains Run in Column A

Sub DeleteSpecific()
Dim cCell As Range
Dim FirstAddress As String

With ActiveSheet.Range("A:A")
    Set cCell = .Find(What:="Run", LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
    If Not cCell Is Nothing Then
        FirstAddress = cCell.Address
        Do
            ActiveSheet.Range("A" & cCell.Row).EntireRow.Delete
            Set cCell = .FindNext(cCell)
        Loop While Not cCell Is Nothing And cCell.Address <> FirstAddress
    End If
End With

End Sub

Open in new window


gowflow
0
Hakan YılmazTechnical Office MEP EngineerCommented:
@gowflow , You can simplify your code as this,
Sub DeleteSpecific()
    Dim cCell As Range
    With ActiveSheet.Range("A:A")
        Set cCell = .Find(What:="Run", lookin:=xlValues, Lookat:=xlWhole, MatchCase:=False)
        Do While Not cCell Is Nothing
            cCell.EntireRow.Delete
            Set cCell = .FindNext()
        Loop
    End With
End Sub

Open in new window

0
Hakan YılmazTechnical Office MEP EngineerCommented:
And i made it a little more general & regular by giving user access to define range, text for looking up and some optional parameters of find function.
Sub DeleteSpecific()
    RegularDeleteSpecific ActiveSheet.Range("A:A"), "Run"
End Sub

Sub RegularDeleteSpecific(ByRef xLookInRange As Range, ByVal xLookFor As String, Optional ByVal xLookIn As XlFindLookIn = xlValues, Optional ByVal xLookAt As XlLookAt = xlWhole, Optional ByVal xMatchCase As Boolean = False)
    Dim cCell As Range
    Set cCell = xLookInRange.Find(What:=xLookFor, lookin:=xLookIn, LookAt:=xLookAt, MatchCase:=xMatchCase)
    Do While Not cCell Is Nothing
        cCell.EntireRow.Delete
        Set cCell = xLookInRange.FindNext()
    Loop
End Sub

Open in new window

0
gowflowCommented:
@Hakan Yilmaz
With all due respect and not to offend in anyway, we are in a forum where if you feel you have a solution that could contribute to answers given and not already given then it is welcomed. Answers that simply changes or modify an answer given does not contribute in providing variety nor add more to what is proposed. Moreover there sometimes could be several possibilities in writing a solution hence changing a proposed solution is just simply not productive.

gowflow
0
Hakan YılmazTechnical Office MEP EngineerCommented:
Everyone can see whats going on with answers given for this question, and i know that.
You gave a nice solution first. I added some more functionality later.
So this thread has more information for future visitors.
It's clear that i modified your code. You call it "simply modifying". But I still call it a "contribution". Because someone can learn different things from my posts.

(Not about this topic, but) What i generally want to do is to make more people learn something from EE, so it is not between "authors" and "solvers" of a thread.
Also, I want more general solutions. In other words, I want to generalize solutions.
So that they will no longer be specific answers for a case.

I'm sorry that i don't know English sufficiently enough to speak more politely, but I'm trying to be polite.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.