Results 1 to 12 of 12
  1. #1
    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

    Delete certain rows but not others (XP)

    Getting a bit stuck here trying to work out my case if and exemptions to a rule.

    I have a list in column D that has a list of estates. This will be a repetitive business process over a period of months. <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>( I suggested the Access DB route but I was told that they wanted it in Excel...who am I to argue? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>)

    The issue is that the users would like to delete the rows that end in Estate, this I can do without a problem but there is a list of exemptions, for this exercise they are "An Estate", "Another Estate" and "Yet Another Estate". Here is my code just where do I put my list of exemptions so they are not deleted?

    <pre>Sub DeleteUnwantedEstate()
    Dim lastrow As Long, r As Long
    '......Do some stuff then do this...

    lastrow = ActiveSheet.UsedRange.Rows.Count

    For r = lastrow To 1 Step -1

    If Right(Cells(r, 4).Value, 6) = "Estate" Then Rows®.Delete

    Next r


    '.... do some more stuff

    End Sub</pre>

    Jerry

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete certain rows but not others (XP)

    <P ID="edit" class=small>(Edited by sdckapr on 15-Jul-06 09:20. Added new line to code for efficiency and PS)</P>How about this?
    Steve

    PS. I added a new line (red) to increase the efficiency. Once an exception is found, there is no reason to check for any mores. Note: this does not check for exceptions at the end. I assumed the exception could occur anyplace in the string. If this is not the case, then it can be adapted to only search expceptions at the end. I presumed you may exceptions that may not even have "Estate" in them.

    <pre>Option Explicit
    Sub DeleteUnwantedEstate()
    Dim lastrow As Long, r As Long
    Dim vExceptions
    Dim x As Integer
    Dim i As Integer
    vExceptions = Array("An Estate", "Another Estate", "Yet Another Estate")
    '......Do some stuff then do this...
    lastrow = ActiveSheet.UsedRange.Rows.Count

    For r = lastrow To 1 Step -1
    If Right(Cells(r, 4).Value, 6) = "Estate" Then
    x = 0
    For i = LBound(vExceptions) To UBound(vExceptions)
    x = x + InStr(Cells(r, 4).Value, vExceptions(i))
    <font color=red> If x > 0 Then Exit For</font color=red>
    Next
    If x = 0 Then
    Rows®.Delete
    End If
    End If
    Next r
    '.... do some more stuff
    End Sub</pre>


  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete certain rows but not others (XP)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I think you meant:
    If Right(Cells(r, 4).Value, len(ExArray(i))) = ExArray(i) Then

    since we know that
    Right(Cells(r, 4).Value, 6) = "Estate"


    Steve

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Delete certain rows but not others (XP)

    Oops, but probably should have been
    If Cells(r, 4).Value = ExArray(i) Then
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Delete certain rows but not others (XP)

    <P ID="edit" class=small>(Edited by macropod on 15-Jul-06 23:36. Error correction - see subsequent discussion)</P>Hi Jerry,

    I'd be inclined to mak an exceptions array (called 'ExArray' in the code below), and run the 'For r = lastrow To 1 Step -1' loop like:
    For r = lastrow To 1 Step -1
    If Right(Cells(r, 4).Value, 6) = "Estate" Then
    DelRow = True
    For n = 1 to ubound(ExArray)
    If Cells(r, 4).Value = ExArray(i) Then
    DelRow = False
    Exit For
    End If
    Next n
    If DelRow = True Then Rows®.Delete
    End If
    Next r
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    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

    Re: Delete certain rows but not others (XP)

    Excellent, thanks Steve and Macropod...had a fiddle , couldn't get it to work and then realised the sample data being extracted from the legacy system was in CAPITALS and therefore I had to ensure the array was capitalised[gggrrr] anyway, got it to work ....it sings thank you.
    Jerry

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete certain rows but not others (XP)

    For those following along at home and need something more explicit, something like:

    InStr(Ucase(Cells(r, 4).Value), Ucase(vExceptions(i)))

    and

    If Ucase(Right(Cells(r, 4).Value, 6)) = "ESTATE" Then

    would make the code be not case sensitive...

    Steve

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete certain rows but not others (XP)

    It depends on if the exception is the entire string, within the string, or only at the end and what you want to test for...

    Steve

  9. #9
    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

    Re: Delete certain rows but not others (XP)

    Steve

    Sorry to harp back to this post but this was the first day that the user was able to test this with a full extracted download. The have a problem that I can't see why it is happening.

    Looking at the exceptions list:

    vExceptions = Array("Funny Estate", "Happy Estate", "Unhappy Estate")


    When they run the code everything works fine until it gets to and estate ( say Funny Estate ) it also keeps The Funny Estate. I thought this code would match up exactly to what was in the code. I have removed the name from the array and it does delete both. Wht would it be letting through the string starting with The? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Jerry

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete certain rows but not others (XP)

    Steve's code uses InStr - this tests whether the exceptions are part of the cell value, not whether they are the entire value. Try Macropod's code, or this version of Steve's code:

    Sub DeleteUnwantedEstate()
    Dim lastrow As Long, r As Long
    Dim vExceptions
    Dim i As Integer
    Dim fDelete As Boolean
    vExceptions = Array("An Estate", "Another Estate", "Yet Another Estate")
    '......Do some stuff then do this...
    lastrow = ActiveSheet.UsedRange.Rows.Count

    For r = lastrow To 1 Step -1
    If LCase(Right(Cells(r, 4).Value, 6)) = "estate" Then
    fDelete = True
    For i = LBound(vExceptions) To UBound(vExceptions)
    If LCase(Cells(r, 4)) = LCase(vExceptions(i)) Then
    fDelete = False
    Exit For
    End If
    Next i
    If fDelete Then
    Rows®.Delete
    End If
    End If
    Next r
    '.... do some more stuff
    End Sub

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete certain rows but not others (XP)

    I am not sure what you are after perhaps.

    The code I proposed will delete anything with "Estate" at the end, except when it contains the text from the exception list. In your example if it "The Funny Estate" contains "Funny Estate" so it is considered an exception.

    If the exceptions have to match the string exactly then you do not need to use the INSTR but can compare the 2 directly (use a UCASE, for non-case sensitive). Instead of using an integer (x) you could use a boolean variable instead.

    Does that help? If not could you expand on your requirements?

    Steve

  12. #12
    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

    Re: Delete certain rows but not others (XP)

    Steve/Hans

    Thanks <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22> I couldn't see the wood for the trees and overlooked the instr(). I was doing another thing at work and had an "emergency call" from the user who needless to say has gone home[gggrrrr] It works fine now and to spec <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

Posting Permissions

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