Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2015
    Posts
    4

    Unanswered: Excel VBA - Delete rows where conditions in Columns have to be met

    Hello,

    Please treat me gently as I am a newbie and not sure I am posting in the correct forum to start with?

    I have an issue with VBA code when trying to delete rows when the following conditions are met:

    In Column "E" Sold to Name does not equal "CASH IN ADVANCE" and In Column "F" Sold to Country does equal "USA"

    I have the following code that doesn't do anything:

    Code:
    row_number = 1
       Do
        DoEvents
            row_number = row_number + 1
            Soldtoname = Sheet1.Range("e" & row_number)
            row_number = row_number + 1
            soldtocountry = Sheet1.Range("f" & row_number)
            
        If InStr(Soldtoname, "cash in advance") <> 1 Then
        If InStr(soldtocountry, "usa") >= 1 Then
        Sheet1.Rows(row_number & ":" & row_number).Delete
        row_number = row_number - 1
        
      End If
        End If
      Loop Until Soldtoname = ""
    End Sub
    Any guidance would be very much appreciated before I am measured for a strait jacket.

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    It is normally best to move up when deleting rows ie. start at the bottom.

    If you start at the top you only need to add a row if you do not delete the row something like this
    Code:
    Sub DeleteRows()
        Dim row_number As Integer
        Dim Soldtoname As String
        Dim soldtocountry As String
        
        row_number = 2
        
        Do
            Soldtoname = Sheet1.Cells(row_number, 5)
            soldtocountry = Sheet1.Cells(row_number, 6)
             
            If InStr(Soldtoname, "cash in advance") = 0 And InStr(soldtocountry, "usa") <> 0 Then
                Sheet1.Rows(row_number).Delete
            Else
                row_number = row_number + 1
            End If
            
         Loop Until Soldtoname = ""
    End Sub
    HTH


    MTB

  3. #3
    Join Date
    Sep 2015
    Posts
    4

    Red face

    Quote Originally Posted by MikeTheBike View Post
    Hi

    It is normally best to move up when deleting rows ie. start at the bottom.

    If you start at the top you only need to add a row if you do not delete the row something like this
    Code:
    Sub DeleteRows()
        Dim row_number As Integer
        Dim Soldtoname As String
        Dim soldtocountry As String
        
        row_number = 2
        
        Do
            Soldtoname = Sheet1.Cells(row_number, 5)
            soldtocountry = Sheet1.Cells(row_number, 6)
             
            If InStr(Soldtoname, "cash in advance") = 0 And InStr(soldtocountry, "usa") <> 0 Then
                Sheet1.Rows(row_number).Delete
            Else
                row_number = row_number + 1
            End If
            
         Loop Until Soldtoname = ""
    End Sub
    HTH


    MTB
    Hi Mike,

    Just wanted you to know that my Excel had crashed and now your macro works like a dream. Thanks
    Last edited by billybea; 10-01-15 at 12:24.

Posting Permissions

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