Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro help (2007)

    G'day loungers!
    I am trying to figure out how to code the following situation in VBA and am hitting a wall. If the value in column U is equal to 9900 or 9915 and the value in column Y is equal to "CB" or "MB" then I want to delete that row. Any suggestions?
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  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

    Re: Macro help (2007)

    Hi Greg

    Try this piece of code:
    <pre>Sub RemoveRows()
    Dim r As Long
    Dim n As Long

    r = Range("A" & Rows.Count).End(xlUp).Row

    For n = r To 1 Step -1

    If Cells(r, 21) = 9900 Or Cells(r, 21) = 9915 _
    And Cells(r, 25) = "CB" Or Cells(r, 25) = "MB" Then
    Cells(r, 1).EntireRow.Delete
    Else
    End If
    Next n
    End Sub

    </pre>

    Jerry

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help (2007)

    Hi Jerry,

    I have tried the code and it is not deleting any rows. I have attached a sample of the data and the macro as I typed it in. I do not see why it is not working. There are 10 rows in the sample data that meet the criteria for deletion.
    Attached Files Attached Files
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

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

    Re: Macro help (2007)

    Just a tiny confusion between r and n. Try

    Sub RemoveRows()
    Dim r As Long
    Dim n As Long
    r = Range("A" & Rows.Count).End(xlUp).Row
    For n = r To 1 Step -1
    If (Cells(n, 21) = 9900 Or Cells(n, 21) = 9915) _
    And (Cells(n, 25) = "CB" Or Cells(n, 25) = "MB") Then
    Cells(n, 1).EntireRow.Delete
    End If
    Next n
    End Sub

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help (2007)

    Hans, Perfect as always! Thank you to both you and Jerry!
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  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: Macro help (2007)

    Whoops

    Sorry, that teach me to send untested code, try:

    <pre>Sub RemoveRows()
    Dim r As Long
    Dim m As Long
    Dim n As Long

    r = Range("A" & Rows.Count).End(xlUp).Row

    For n = r To 1 Step -1

    If Cells(n, 21) = 9900 Or Cells(n, 21) = 9915 _
    And Cells(n, 25) = "CB" Or Cells(n, 25) = "MB" Then
    Cells(n, 1).EntireRow.Delete
    Else
    End If
    Next n
    End Sub



    </pre>

    Jerry

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

    Re: Macro help (2007)

    You also need parentheses around the ... Or ... pairs because And has precedence above Or.

  8. #8
    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: Macro help (2007)

    <img src=/S/cool.gif border=0 alt=cool width=15 height=15> thanks...
    Jerry

  9. #9
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help (2007)

    So regarding Hans post about the parantheses, is this the correct placement?

    If (Cells(n,21) = 9900 or Cells(n,21) = 9915) _
    And (Cells(n,25) = "CB" or Cells(n,25) = "MB") Then
    Cells(n,1).EntreRow.Delete
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help (2007)

    Yes, as per Hans post <post#=727,185>post 727,185</post#>

  11. #11
    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: Macro help (2007)

    Correct

    I was rushing my coding so bypassed that issue but it makes good logic to break it down in this way as the parenthesis set precedence similar to the old conundrum of:

    What do the following equal
    1) 2*3+1
    2) 2*(3+1)
    3) (2*3)+1

    Same as

    If ( A=1 or A=2) and (B=3 or B=4)

    The code will initially check the values in the the lefthand bracket and then check the values in the righthand set. By breaking this down it allows the code to identify the correct precedence for checking values.

    So yes you are correct <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    If (Cells(n,21) = 9900 or Cells(n,21) = 9915) _
    And (Cells(n,25) = "CB" or Cells(n,25) = "MB") Then
    Cells(n,1).EntreRow.Delete
    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
  •