CODEWITHSUNDEEP
vbaexcel
Artur Rutkowski
Artur Rutkowski

Reputation: 537

My loop won't end

1) I have a porblem with my FOR loop. I goes ad infinitum. And this is the first problem of mine on which I ask your help.
2) Second problem is that I don't know how to add more then one conditon in "If"
My questions are in the code as comments. 

Sub Repurchase_upload()

Dim Worksheet As Worksheets
startrow = Worksheets("GUTS").Cells(10, 1) 'Here I denote 1
endrow = Worksheets("GUTS").Cells(11, 1) 'Here I denote 1000
For x = startrow To endrow

            If Cells(x, "A").Value <> "DU" Then 'I would like it to look like that: 'If Cells(x, "A").Value <> "DU" or "DR" or "EK" Then' but I don't know how to do this 'or'
            Cells(x, "A").EntireRow.ClearContents
            End If 'And here it won't end...
    Next
End Sub

Upvotes: 0

Views: 402

Answers (2)

digEmAll
digEmAll

Reputation: 57210

The multiple condition is easy to implement:

For x = startrow To endrow
    If Cells(x, "A").Value <> "DU" Or Cells(x, "A").Value <> "DR" Or Cells(x, "A").Value <> "EK" Then 
        Cells(x, "A").EntireRow.ClearContents
    End If
Next x

For the infinite loop I don't know, it seems fine to me... isn't simply too slow ?
Maybe you should disable the screen updating before calling ClearContents a thousand times e.g. :

Sub Repurchase_upload()

    Application.ScreenUpdating = False 'Disable screen updating

    Dim Worksheet As Worksheets
    startrow = Worksheets("GUTS").Cells(10, 1) 'Here I denote 1
    endrow = Worksheets("GUTS").Cells(11, 1) 'Here I denote 1000
    For x = startrow To endrow
        If Cells(x, "A").Value <> "DU" Or Cells(x, "A").Value <> "DR" Or Cells(x, "A").Value <> "EK" Then 
            Cells(x, "A").EntireRow.ClearContents
        End If
    Next x

    Application.ScreenUpdating = True  'Re-enable screen updating

End Sub

Upvotes: 1

grantnz
grantnz

Reputation: 7423

Another way of doing the multiple value test is to use the select statement as shown below:

Sub Repurchase_upload()

    Dim Worksheet As Worksheets
    startrow = Worksheets("GUTS").Cells(10, 1) 'Here I denote 1
    endrow = Worksheets("GUTS").Cells(11, 1) 'Here I denote 1000
    For x = startrow To endrow
        Select Case Cells(x, "A").Value
        Case "DU", "DR", "EK"
          'Do nothing
        Case Else
            Cells(x, "A").EntireRow.ClearContents
        End Select
    Next
End Sub

Upvotes: 2

Related Questions