Deleting rows that dont...
 

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

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

Deleting rows that dont...
 

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

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

Deleting rows that dont...
 

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?

On Thu, 23 Dec 2004 23:38:09 -0600, "Dominique Feteau"
wrote:

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

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

Deleting rows that dont...
 

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: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.

---
Regards,
Norman



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

Deleting rows that dont...
 

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

---
Regards,
Norman



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

Deleting rows that dont...
 

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: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

---
Regards,
Norman



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

thanx