Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all rows except
need to delete all rows except those containing "apples" and or
"oranges" in column G. code below works, just need to add second criteria. assuming that "a" would be changed to "g". Sub Delete_Row() For i = Cells(Rows.Count, "a"). _ End(xlUp).Row To 2 Step -1 If InStr(UCase(Cells(i, "a")), "MZ")<1 _ Then Rows(i).Delete Next i End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all rows except
Sub Delete_Row()
For i = Cells(Rows.Count, "a"). _ End(xlUp).Row To 2 Step -1 If InStr(UCase(Cells(i, "a")), "xxx")<1 _ or InStr(UCase(Cells(i, "a")), "yyy")<1 then _ Rows(i).Delete Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "J.W. Aldridge" wrote in message ... need to delete all rows except those containing "apples" and or "oranges" in column G. code below works, just need to add second criteria. assuming that "a" would be changed to "g". Sub Delete_Row() For i = Cells(Rows.Count, "a"). _ End(xlUp).Row To 2 Step -1 If InStr(UCase(Cells(i, "a")), "MZ")<1 _ Then Rows(i).Delete Next i End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all rows except
Hi J.W. Aldridge
One way is to use AutoFilter Test this on a copy of your workbook I use < in the filter so it not delete DeleteValue1 and DeleteValue12 Sub Delete_with_Autofilter_Two_Criteria() Dim DeleteValue1 As String Dim DeleteValue2 As String Dim rng As Range Dim calcmode As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Fill in the two values that you want to delete DeleteValue1 = "apples" DeleteValue2 = "oranges" 'Sheet with the data, you can also use Sheets("MySheet") With ActiveSheet 'Firstly, remove the AutoFilter .AutoFilterMode = False 'Apply the filter .Range("G1:G" & .Rows.Count).AutoFilter Field:=1, _ Criteria1:="<" & DeleteValue1, Operator:=xlAnd, Criteria2:="<" & DeleteValue2 With .AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With 'Remove the AutoFilter .AutoFilterMode = False End With With Application .ScreenUpdating = True .Calculation = calcmode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J.W. Aldridge" wrote in message ... need to delete all rows except those containing "apples" and or "oranges" in column G. code below works, just need to add second criteria. assuming that "a" would be changed to "g". Sub Delete_Row() For i = Cells(Rows.Count, "a"). _ End(xlUp).Row To 2 Step -1 If InStr(UCase(Cells(i, "a")), "MZ")<1 _ Then Rows(i).Delete Next i End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all rows except
....unfortnately, this one didn't work.
it deleted all of the rows (although the criteria was met) and left no rows existing. I went back and ran the original code and it filtered and deleted it correctly (but only one criteria of course). any suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all rows except
Pardon me...
Ron, the code you suggested did work. Thanx both of you... You always seem to be right there when we need ya! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all rows except
I believe you wanted to use And instead of Or for the operator. By using
Or, if one is true then the entire If statement = true and it will delete. So if you have Apples in one it would nor met the criteria, but the absence of Oranges would would meet the criteria, so it would delete. Using And would require that both Apples and Oranges be absent before it deletes. "J.W. Aldridge" wrote in message ... ...unfortnately, this one didn't work. it deleted all of the rows (although the criteria was met) and left no rows existing. I went back and ran the original code and it filtered and deleted it correctly (but only one criteria of course). any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows if any cell in Column H is blank but do not Delete Fir | Excel Programming | |||
Copy pasting Rows, but need to Delete any Shapes/Pictures that are within copied rows | Excel Programming | |||
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 |