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?
 
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
 
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.