Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Locking a couple rows so when you do a delete shift row they dont | Excel Discussion (Misc queries) | |||
how do i delete the extra empty rows and columns that i dont need. | Excel Discussion (Misc queries) | |||
rows that dont want to be deleted!!!! | Excel Programming |