mato01
asked on
Delete all rows based on values in a table
In Excel 2007, I am using the below Sub to delete all rows that have the word "SODA -" in Column C. Is there a way that I could use an external excel table for the values. This would allow adding new values to be deleted without having to go into the Sub.
For example, the table would look like
COLUMN C
SODA -
CANDY -
CHIPS -
COOKIES -
Then the Sub would delete all rows if the values was in the table. It would delete "SODA -", "CANDY - ", "CHIPS -", and "COOKIES -".
************************** ********** ********** *********
Sub Step1_DeleteRowsWithoutSOD AinColC()
' This macro deletes all rows on the active worksheet
' that do not have (SODA-) in column C.
Dim rng As Range, cell As Range, del As Range
Dim strCellValue As String
Set rng = Intersect(Range("C2:C65000 "), ActiveSheet.UsedRange)
For Each cell In rng
strCellValue = (cell.Value)
If InStr(strCellValue, "SODA -") = 0 Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub
For example, the table would look like
COLUMN C
SODA -
CANDY -
CHIPS -
COOKIES -
Then the Sub would delete all rows if the values was in the table. It would delete "SODA -", "CANDY - ", "CHIPS -", and "COOKIES -".
**************************
Sub Step1_DeleteRowsWithoutSOD
' This macro deletes all rows on the active worksheet
' that do not have (SODA-) in column C.
Dim rng As Range, cell As Range, del As Range
Dim strCellValue As String
Set rng = Intersect(Range("C2:C65000
For Each cell In rng
strCellValue = (cell.Value)
If InStr(strCellValue, "SODA -") = 0 Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub
PS - I've found that the .UsedRange property is not always accurate, so an alternative that I almost always use to substitute for:
Set rng = Intersect(Range("C2:C" & Rows.Count), ActiveSheet.UsedRange)
Is:
Set rng = Range("C2",Range("C" & Rows.Count).End(xlUp))
This will always ensure the last cell in rng is one that has data, rather than an incorrect UsedRange lower boundary.
Note, also, the UsedRange boundary can also be Excel-defined by formatting and cells that have no data at all!
Dave
Set rng = Intersect(Range("C2:C" & Rows.Count), ActiveSheet.UsedRange)
Is:
Set rng = Range("C2",Range("C" & Rows.Count).End(xlUp))
This will always ensure the last cell in rng is one that has data, rather than an incorrect UsedRange lower boundary.
Note, also, the UsedRange boundary can also be Excel-defined by formatting and cells that have no data at all!
Dave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Table: Range name called "Goodies"
Open in new window
See attached.
Dave
delGoodies-r1.xls