Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows where cells contain various criteria
Hello,
I am working on a macro that will delete a varying number of rows based on certain criteria. Every row is listed twice. What I do now is sort the columns to put all the duplicates together and have a simple if formula to put a 1 next to every row on it's second occurance. What I need to do is Delete all of the rows where a 1 occurs in column A Delete all the rows where "CA_" appears at the left of column B Delete all Rows where "CA-" appears ANYWHERE in the value of column B Delete all rows where "Undef_" appears on the left of Col B Delete all rows where "Target_" appears on the left of Col B I currently having these as separate steps which is adding a lot of time to the macro, and actually shut my computer down yesterday while running it Is there a way to combine the deletion crieteria to make it easier? Here is some of my code: Range("B27").Activate Do If Left(ActiveCell.Value, 3) = "CA_" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) ActiveCell.Offset(-2, 0).Select Do If Left(ActiveCell.Value, 6) = "Undef_" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) ActiveCell.Offset(-2, 0).Select Do If Left(ActiveCell.Value, 7) = "Target_" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) and With ActiveSheet .Rows(1).Insert .Range(MYCOL & 1).Value = "Temp" .UsedRange With Intersect(.Columns(MYCOL), .UsedRange) .AutoFilter Field:=1, Criteria1:="*CA-*" .SpecialCells(xlCellTypeVisible).EntireRow.Delete End With .UsedRange End With and Range("B27").Select Do If Left(ActiveCell.Value, 1) = 1 Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) Any help would be appreciated, Glen |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete all rows if criteria not matched | Excel Discussion (Misc queries) | |||
Delete rows based on certain criteria | Excel Discussion (Misc queries) | |||
Delete Rows where cells does not meet criteria | Excel Worksheet Functions | |||
delete rows with criteria | Excel Programming | |||
Delete rows w/o criteria | Excel Programming |