Deleting rows that dont...

[Dominique Feteau]

I found some code in this forum that would delete rows if the value given
matches any of the cells on a worksheet. I was wondering how I could take
it so far as to delete any rows that arent in a list of values. for example
if i have a defined name called "forms" which reside on another page called
"DocList", any values in the rows in column D that dont match that list on
DocList, can that whole row be deleted.

niq

Sub DelRows()
Dim uRange As Range
Dim delRange As Range
Set uRange = Worksheets("CLSEXCEL").UsedRange
Set delRange = uRange.Cells(uRange.Rows.Count + 1, 1).EntireRow
For Each c In uRange.Cells
If InStr(1, c.Value, "POJORD") > 0 Then
Set delRange = Union(delRange, c.EntireRow)
End If
Next c
delRange.Delete
End Sub

 

[Norman Jones]

Hi Dominique,

Try:

Sub DelRows2()
Dim uRange As Range
Dim delRange As Range
Dim cell As Range
Dim rw As Range
Dim blKeep As Boolean

Set uRange = Worksheets("CLSEXCEL").UsedRange
Set delRange = uRange.Cells(uRange.Rows.Count + 1, 1).EntireRow
For Each rw In uRange.Rows
blKeep = False
For Each cell In Worksheets("DocList").Range("Forms")
If Application.CountIf(rw, cell.Value) > 0 Then
blKeep = True
Exit For
End If
Next cell
If Not blKeep Then
Set delRange = Union(delRange, rw.EntireRow)
End If
Next rw
delRange.Delete
End Sub

 

[Dominique Feteau]

It didnt work. it ended up deleting everything. is there something else i
need to do? I have my defined name. not sure on why its not working

 

[Myrna Larson]

You said in your original message that you wanted to delete the row if the
value in column D in that row is not in the Forms list. But in your code, you
are keeping the row if any value in Forms is found anywhere in the row. Is
that what you really want?

Have you stepped through the code with F8 to find the problem?

 

[Norman Jones]

Hi Dominique,

As Myrna points out, my code retained rows if items from the named list
occur anywhere in the row rather than in column D, as specified. I read your
request insufficiently carefully.

Try, therefore the following revised code:

Sub DelRows3()
Dim uRange As Range
Dim delRange As Range
Dim cell As Range
Dim rCell As Range
Dim blKeep As Boolean

Set uRange = Worksheets("CLSEXCEL").UsedRange
Set delRange = uRange.Cells _
(uRange.Rows.Count + 1, 1).EntireRow
For Each rCell In Intersect(uRange, Columns("D"))
blKeep = False
For Each cell In Worksheets("DocList").Range("Forms")
If rCell.Value = cell.Value Then
blKeep = True
Exit For
End If
Next cell
If Not blKeep Then
Set delRange = Union(delRange, rCell.EntireRow)
End If
Next rCell
delRange.Delete
End Sub

Providing I had a range named "Forms" on a sheet named "DocList", the code
worked for me.

 

[Dominique Feteau]

this works great. works perfectly. is there anyway that we can keep the
first row? its no biggie, but that would be perfect.

thanx

 

[Norman Jones]

Hi Dominique,

I have amended the sub to keep the first row and taken the opportunity
properly to qualify the intersect range:

Sub DelRows3a()
Dim uRange As Range
Dim delRange As Range
Dim cell As Range
Dim rCell As Range
Dim blKeep As Boolean

With Worksheets("CLSEXCEL")
Set uRange = .UsedRange
Set delRange = uRange.Cells _
(uRange.Rows.Count + 1, 1).EntireRow
For Each rCell In Intersect(uRange, .Columns("D"))
blKeep = False
For Each cell In Worksheets("DocList").Range("Forms")
If rCell.Value = cell.Value Or rCell.Row = 1 Then
blKeep = True
Exit For
End If
Next cell
If Not blKeep Then
Set delRange = Union(delRange, rCell.EntireRow)
End If
Next rCell
End With
delRange.Delete
End Sub