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

    Deleting rows based on values in two cells (2007)

    Good morning,
    I am trying to write a macro that will delete a row if the value in column "Z" is 3200 AND the value in column "F" is "-----". I have tried using samples of code from this forum that delete rows and found they work well for the rows where I have only one criteria to meet for deletion, but the second criteria has me baffled. Here is the code that I am currently trying but I am getting a compile error: Next without For and cannot see why.
    Dim r As Long
    Dim n As Long
    Dim o As Long

    n = Range("G65536").End(xlUp).Row
    For r = n To 2 Step -1
    If Trim(Range("G" & r)) < 3000 Then
    Range("G" & r).EntireRow.Delete
    End If
    Next r
    For r = n To 2 Step -1
    If Trim(Range("G" & r)) > 3899 Then
    Range("G" & r).EntireRow.Delete
    End If
    Next r
    For r = n To 2 Step -1
    If Trim(Range("G" & r)) = 3200 Then
    If Trim(Range("F" & r) = "-----") Then
    Range("G" & r).EntireRow.Delete
    End If
    Next r

    I have three questions based on this code.
    #1 Will it actually delete a row that has two criteria?
    #2 Why am I getting the compile error?
    #3 Is there a way to streamline the code?
    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: Deleting rows based on values in two cells (20

    As for #2

    You are missing a final End If:

    ...
    End If
    End If
    Next r
    End Sub
    Jerry

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

    Re: Deleting rows based on values in two cells (2007)

    Jezza already pointed out the omission in your code. You could combine the conditions as follows:
    <code>
    For r = n To 2 Step -1
    If Trim(Range("G" & r)) < 3000 Or Trim(Range("G" & r)) > 3899 Or _
    Trim(Range("G" & r)) = 3200 And Trim(Range("F" & r) = "-----") Then
    Range("G" & r).EntireRow.Delete
    End If
    Next r</code>

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

    Re: Deleting rows based on values in two cells (2007)

    Thank you to both Jezza and Hans. The code worked beautifuly.

    Now back to custonizing the ribbon for this add-in... <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

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