Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 425
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,124
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 425
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 425
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,565
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete Rows if any cell in Column H is blank but do not Delete Fir manfareed Excel Programming 4 September 28th 07 05:20 PM
Copy pasting Rows, but need to Delete any Shapes/Pictures that are within copied rows Corey Excel Programming 2 August 1st 07 02:02 AM
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows Scott Excel Worksheet Functions 0 December 13th 06 02:25 AM
How to delete rows when List toolbar's "delete" isnt highlighted? Linda Excel Worksheet Functions 1 May 26th 05 08:39 PM
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 Annette[_4_] Excel Programming 2 September 21st 04 02:40 PM


All times are GMT +1. The time now is 03:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"