Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    Set w3 = Sheets("65-20's")
    w3.Activate
    x3 = w3.Cells(1, 10).End(xlDown).Row		'65-20's last value (row number)
    Set y3 = w3.Range("N2:N" & x3)			  '0808 status column
    
    	For z3 = x3 To 2 Step -1
    	Set RngFound = y3.Find(What:="Purged Found", LookIn:=xlValues, LookAt:=xlWhole)
    	If RngFound Is Nothing Then
    		w3.Rows(z3).Delete
    		x3 = x3 - 1
    	End If
    	Next z3
    If RngFound Is Nothing Then <<<<<< This does not delete anything at all.
    If Not RngFound Is Nothing Then <<<<<< This deletes everything that is "Purged Found"

    How do I delete everything other than "Purged Found" ??

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Assuming the string Purged Found is always found in Column N then:

    [codebox]Set w3 = Sheets("65-20's")
    w3.Activate
    x3 = w3.Cells(1, 10).End(xlDown).Row '65-20's last value (row number)
    'Set y3 = w3.Range("N2:N" & x3) '0808 status column

    For z3 = x3 To 2 Step -1
    If Cells(z3, 14).Value <> "Purged Found" Then
    w3.Rows(z3).Delete
    x3 = x3 - 1
    End If
    Next z3[/codebox]
    Jerry

  3. #3

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    And another one..........

    For z5 = x5 To 2 Step -1
    If Range("M" & z5) = "" Or Left(("K" & z5), 2) = "11" Or Range("M" & z5) = "" Or Left(("K" & z5), 2) = "80" Then
    Range("M" & z5).EntireRow.Delete
    End If
    Next z5


    I always get confused with my and/or's. I need to delete if M is blank and/or if K begins with 11 or 80. This is deleting all the blanks but not the 11/80's ??

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There's no point in duplicating the condition Range("M" & z5) = "". It's either satisfied or not satisfied. Using the condition twice won't change that.
    Perhaps the values in column K don't really begin with 11 or 80? For example, if there's a space before the 11 or 80, you'll miss those.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •