how do I delete all rows that match a condition?

I want to record a macro to delete all rows in a worksheet where a specified
column entry matches a condition (e.g. "where column D=DONCASTER or LEEDS or
HALIFAX")

djhs,

The typical code to do that is:

On Error Resume Next
Columns("D:D").AutoFilter Field:=1, Criteria1:="DONCASTER"
Range("D2:D65536").SpecialCells(xlCellTypeVisible).EntireRow.Delete

Repeat the two lines above for each condition, then use the line

Columns("D:D").AutoFilter Field:=1

to turn off the filter, and your macro is done.

HTH,
Bernie
MS Excel MVP

"djhs63" <djhs63@discussions.microsoft.com> wrote in message
news:3E753F67-92EB-4C5A-829D-98D2129ED04F@microsoft.com...
> I want to record a macro to delete all rows in a worksheet where a
specified
> column entry matches a condition (e.g. "where column D=DONCASTER or LEEDS
or
> HALIFAX")

Bernie,

thanks very much - is there any way to concatenate conditions in one
statement, like Criteria1:="DONCASTER or HALIFAX or LEEDS"?

Regards,
djhs

"Bernie Deitrick" wrote:

> djhs,
>
> The typical code to do that is:
>
> On Error Resume Next
> Columns("D:D").AutoFilter Field:=1, Criteria1:="DONCASTER"
> Range("D2:D65536").SpecialCells(xlCellTypeVisible).EntireRow.Delete
>
> Repeat the two lines above for each condition, then use the line
>
> Columns("D:D").AutoFilter Field:=1
>
> to turn off the filter, and your macro is done.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "djhs63" <djhs63@discussions.microsoft.com> wrote in message
> news:3E753F67-92EB-4C5A-829D-98D2129ED04F@microsoft.com...
> > I want to record a macro to delete all rows in a worksheet where a
> specified
> > column entry matches a condition (e.g. "where column D=DONCASTER or LEEDS
> or
> > HALIFAX")
>
>
>

djhs,

You can do only two at a time (using OR - record a macro for the syntax), so
it's easier to just copy and paste, or loop through using a variable as the
parameter.

HTH,
Bernie
MS Excel MVP


"djhs63" <djhs63@discussions.microsoft.com> wrote in message
news:41B26200-5DD9-4499-957D-7F0A54745301@microsoft.com...
> Bernie,
>
> thanks very much - is there any way to concatenate conditions in one
> statement, like Criteria1:="DONCASTER or HALIFAX or LEEDS"?
>
> Regards,
> djhs
>
> "Bernie Deitrick" wrote:
>
>> djhs,
>>
>> The typical code to do that is:
>>
>> On Error Resume Next
>> Columns("D:D").AutoFilter Field:=1, Criteria1:="DONCASTER"
>> Range("D2:D65536").SpecialCells(xlCellTypeVisible).EntireRow.Delete
>>
>> Repeat the two lines above for each condition, then use the line
>>
>> Columns("D:D").AutoFilter Field:=1
>>
>> to turn off the filter, and your macro is done.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "djhs63" <djhs63@discussions.microsoft.com> wrote in message
>> news:3E753F67-92EB-4C5A-829D-98D2129ED04F@microsoft.com...
>> > I want to record a macro to delete all rows in a worksheet where a
>> specified
>> > column entry matches a condition (e.g. "where column D=DONCASTER or
>> > LEEDS
>> or
>> > HALIFAX")
>>
>>
>>

Hi,

Another way of doing the same:

Sub Test()
With ActiveSheet
For i = .Cells(.Rows.Count, "D").End(xlUp).Row To 2 Step -1
Select Case .Cells(i, "D").Value
Case "DONCASTER", "HALIFAX", "LEEDS"
If rng <> "" Then rng = rng & ","
rng = rng & .Cells(i, "D").Address
End Select
Next i
.Range(rng).EntireRow.Delete
End With
End Sub

Regards,
KL

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:Oz16upNKFHA.1308@TK2MSFTNGP15.phx.gbl...
> djhs,
>
> You can do only two at a time (using OR - record a macro for the syntax),
> so it's easier to just copy and paste, or loop through using a variable as
> the parameter.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "djhs63" <djhs63@discussions.microsoft.com> wrote in message
> news:41B26200-5DD9-4499-957D-7F0A54745301@microsoft.com...
>> Bernie,
>>
>> thanks very much - is there any way to concatenate conditions in one
>> statement, like Criteria1:="DONCASTER or HALIFAX or LEEDS"?
>>
>> Regards,
>> djhs
>>
>> "Bernie Deitrick" wrote:
>>
>>> djhs,
>>>
>>> The typical code to do that is:
>>>
>>> On Error Resume Next
>>> Columns("D:D").AutoFilter Field:=1, Criteria1:="DONCASTER"
>>> Range("D2:D65536").SpecialCells(xlCellTypeVisible).EntireRow.Delete
>>>
>>> Repeat the two lines above for each condition, then use the line
>>>
>>> Columns("D:D").AutoFilter Field:=1
>>>
>>> to turn off the filter, and your macro is done.
>>>
>>> HTH,
>>> Bernie
>>> MS Excel MVP
>>>
>>> "djhs63" <djhs63@discussions.microsoft.com> wrote in message
>>> news:3E753F67-92EB-4C5A-829D-98D2129ED04F@microsoft.com...
>>> > I want to record a macro to delete all rows in a worksheet where a
>>> specified
>>> > column entry matches a condition (e.g. "where column D=DONCASTER or
>>> > LEEDS
>>> or
>>> > HALIFAX")
>>>
>>>
>>>
>
>

KT thanks - please could you suggest syntax for the condition where contents
of column D are "not equal to DONCASTER, LEEDS, HALIFAX"

"Bernie Deitrick" wrote:

> djhs,
>
> You can do only two at a time (using OR - record a macro for the syntax), so
> it's easier to just copy and paste, or loop through using a variable as the
> parameter.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "djhs63" <djhs63@discussions.microsoft.com> wrote in message
> news:41B26200-5DD9-4499-957D-7F0A54745301@microsoft.com...
> > Bernie,
> >
> > thanks very much - is there any way to concatenate conditions in one
> > statement, like Criteria1:="DONCASTER or HALIFAX or LEEDS"?
> >
> > Regards,
> > djhs
> >
> > "Bernie Deitrick" wrote:
> >
> >> djhs,
> >>
> >> The typical code to do that is:
> >>
> >> On Error Resume Next
> >> Columns("D:D").AutoFilter Field:=1, Criteria1:="DONCASTER"
> >> Range("D2:D65536").SpecialCells(xlCellTypeVisible).EntireRow.Delete
> >>
> >> Repeat the two lines above for each condition, then use the line
> >>
> >> Columns("D:D").AutoFilter Field:=1
> >>
> >> to turn off the filter, and your macro is done.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >> "djhs63" <djhs63@discussions.microsoft.com> wrote in message
> >> news:3E753F67-92EB-4C5A-829D-98D2129ED04F@microsoft.com...
> >> > I want to record a macro to delete all rows in a worksheet where a
> >> specified
> >> > column entry matches a condition (e.g. "where column D=DONCASTER or
> >> > LEEDS
> >> or
> >> > HALIFAX")
> >>
> >>
> >>
>
>
>