Deleting rows that dont...
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
---
Regards,
Norman
"Dominique Feteau" wrote in message
...
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
|