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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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")