Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows
Can this code be modified so that ans = 2 things, "inactive" and "closed"?
The code currently finds the word "inactive" in column 12 and deletes the row. I would like it to find both "inactive" and "closed", and delete those rows. Thanks! ans = "Inactive" With Columns(12) Do Set c = .Find(ans, LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete End If Loop While Not c Is Nothing End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows
There is really no advantage to doing an and since you are just doing a find.
You are only looking at matches so just run the code twice. It will do exactly the dame amount of work. That is the beauty of doing .Find. If you were traversing through Column 12 looking at each cell then that would be a very different story. Looping twice in that case would take twice as long. ans = "Inactive" With Columns(12) Do Set c = .Find(ans, LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete End If Loop While Not c Is Nothing End With ans = "Closed" With Columns(12) Do Set c = .Find(ans, LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete End If Loop While Not c Is Nothing End With HTH "Steph" wrote: Can this code be modified so that ans = 2 things, "inactive" and "closed"? The code currently finds the word "inactive" in column 12 and deletes the row. I would like it to find both "inactive" and "closed", and delete those rows. Thanks! ans = "Inactive" With Columns(12) Do Set c = .Find(ans, LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete End If Loop While Not c Is Nothing End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows
Just repeat the loop a second time:
' YOUR CODE, then... ans = "Closed" With Columns(12) Do Set c = .Find(ans, LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete End If Loop While Not c Is Nothing End With "Steph" wrote: Can this code be modified so that ans = 2 things, "inactive" and "closed"? The code currently finds the word "inactive" in column 12 and deletes the row. I would like it to find both "inactive" and "closed", and delete those rows. Thanks! ans = "Inactive" With Columns(12) Do Set c = .Find(ans, LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete End If Loop While Not c Is Nothing End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows
Simple enough. Thanks!
"Jim Thomlinson" wrote in message ... There is really no advantage to doing an and since you are just doing a find. You are only looking at matches so just run the code twice. It will do exactly the dame amount of work. That is the beauty of doing .Find. If you were traversing through Column 12 looking at each cell then that would be a very different story. Looping twice in that case would take twice as long. ans = "Inactive" With Columns(12) Do Set c = .Find(ans, LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete End If Loop While Not c Is Nothing End With ans = "Closed" With Columns(12) Do Set c = .Find(ans, LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete End If Loop While Not c Is Nothing End With HTH "Steph" wrote: Can this code be modified so that ans = 2 things, "inactive" and "closed"? The code currently finds the word "inactive" in column 12 and deletes the row. I would like it to find both "inactive" and "closed", and delete those rows. Thanks! ans = "Inactive" With Columns(12) Do Set c = .Find(ans, LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete End If Loop While Not c Is Nothing End With |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows
Try
http://www.rondebruin.nl/delete.htm#Find -- Regards Ron de Bruin http://www.rondebruin.nl "Steph" wrote in message ... Can this code be modified so that ans = 2 things, "inactive" and "closed"? The code currently finds the word "inactive" in column 12 and deletes the row. I would like it to find both "inactive" and "closed", and delete those rows. Thanks! ans = "Inactive" With Columns(12) Do Set c = .Find(ans, LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete End If Loop While Not c Is Nothing End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |