Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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") |
#2
|
|||
|
|||
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") |
#3
|
|||
|
|||
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") |
#4
|
|||
|
|||
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") |
#5
|
|||
|
|||
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") |
#6
|
|||
|
|||
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete repeating rows | Excel Worksheet Functions | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) | |||
How to delete duplicate rows in Excel 2000? | Excel Discussion (Misc queries) | |||
How do I delete blank rows at the bottom of a spreadsheet to get . | Excel Discussion (Misc queries) | |||
Listing Multiple Rows from Match | Excel Worksheet Functions |