• Macro help (2007)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Macro help (2007)

    Author
    Topic
    WSgvanhook
    AskWoody Lounger
    August 13, 2008 at 5:19 am #453148

    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?

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSJezza
      AskWoody Lounger
      August 13, 2008 at 6:23 am #1121186

      Hi Greg

      Try this piece of code:

      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
      Reply | Quote
      • WSgvanhook
        AskWoody Lounger
        August 13, 2008 at 6:56 am #1121191

        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.

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          August 13, 2008 at 7:00 am #1121193

          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

          Reply | Quote
        • WSJezza
          AskWoody Lounger
          August 13, 2008 at 7:09 am #1121198

          Whoops

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

          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
          Reply | Quote
          • WSHansV
            AskWoody Lounger
            August 13, 2008 at 7:10 am #1121200

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

            Reply | Quote
          • WSgvanhook
            AskWoody Lounger
            August 13, 2008 at 7:37 am #1121209

            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

            Reply | Quote
            • WSVegasNath
              AskWoody Lounger
              August 13, 2008 at 7:46 am #1121211

              Yes, as per Hans post post 727,185

              Reply | Quote
            • WSJezza
              AskWoody Lounger
              August 13, 2008 at 7:57 am #1121215

              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 grin

              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

              Reply | Quote
    Viewing 0 reply threads
    Reply To: Macro help (2007)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    April 2025
    S M T W T F S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.