VBA Loop Help

[ExcelKid_10]

Hi All-

Can somebody help me with this code. I cannot get it to loop through for each criteria (Str1, Str2) and delete the rows. I have to run the code 7 times to get them all cleared. Any thoughts are much appreciated as always! Thanks! EK

Sub CheckValues2()
Dim rwIndex As Integer
Dim colIndex As Integer
Dim Str1 As String
Dim Str2 As String
Dim Str3 As String
Dim Str4 As String
Dim Str5 As String
Dim Str6 As String
Dim Str7 As String

Str1 = "Average/Total "
Str2 = "Median "
Str3 = "Max "
Str4 = "25th Percentile "
Str5 = "50th Percentile "
Str6 = "75th Percentile "
Str7 = "Min "

For rwIndex = 1 To 1500
For colIndex = 2 To 2
If Cells(rwIndex, colIndex).Value = Str1 Then
Cells(rwIndex, colIndex).EntireRow.Delete
ElseIf Cells(rwIndex, colIndex).Value = Str2 Then
Cells(rwIndex, colIndex).EntireRow.Delete
ElseIf Cells(rwIndex, colIndex).Value = Str3 Then
Cells(rwIndex, colIndex).EntireRow.Delete
ElseIf Cells(rwIndex, colIndex).Value = Str4 Then
Cells(rwIndex, colIndex).EntireRow.Delete
ElseIf Cells(rwIndex, colIndex).Value = Str5 Then
Cells(rwIndex, colIndex).EntireRow.Delete
ElseIf Cells(rwIndex, colIndex).Value = Str6 Then
Cells(rwIndex, colIndex).EntireRow.Delete
ElseIf Cells(rwIndex, colIndex).Value = Str7 Then
Cells(rwIndex, colIndex).EntireRow.Delete

End If

Next colIndex
Next rwIndex
End Sub

 

[iggydarsa]

instead of this

For rwIndex = 1 To 1500
For colIndex = 2 To 2
If Cells(rwIndex, colIndex).Value = Str1 Then
Cells(rwIndex, colIndex).EntireRow.Delete
ElseIf Cells(rwIndex, colIndex).Value = Str2 Then
Cells(rwIndex, colIndex).EntireRow.Delete
ElseIf Cells(rwIndex, colIndex).Value = Str3 Then
Cells(rwIndex, colIndex).EntireRow.Delete
ElseIf Cells(rwIndex, colIndex).Value = Str4 Then
Cells(rwIndex, colIndex).EntireRow.Delete
ElseIf Cells(rwIndex, colIndex).Value = Str5 Then
Cells(rwIndex, colIndex).EntireRow.Delete
ElseIf Cells(rwIndex, colIndex).Value = Str6 Then
Cells(rwIndex, colIndex).EntireRow.Delete
ElseIf Cells(rwIndex, colIndex).Value = Str7 Then
Cells(rwIndex, colIndex).EntireRow.Delete

End If

Next colIndex
Next rwIndex

maybe you should try this

For rwIndex = 1500 To 1 Step -1
If Cells(rwIndex, 2).Value = Str1 or Cells(rwIndex, 2).Value = Str2 or Cells(rwIndex, 2).Value = Str3 or Cells(rwIndex, 2).Value = Str4 or Cells(rwIndex, 2).Value = Str5 or Cells(rwIndex, 2).Value = Str6 or Cells(rwIndex, 2).Value = Str7 Then
    Cells(rwIndex, 2).EntireRow.Delete
End If
Next rwIndex

 

[RoryA]

You could use this:

Sub CheckValues2()
Dim rwIndex As Integer
Dim colIndex As Integer
Dim Str1 As String
Dim Str2 As String
Dim Str3 As String
Dim Str4 As String
Dim Str5 As String
Dim Str6 As String
Dim Str7 As String

Str1 = "Average/Total "
Str2 = "Median "
Str3 = "Max "
Str4 = "25th Percentile "
Str5 = "50th Percentile "
Str6 = "75th Percentile "
Str7 = "Min "

For rwIndex = 1 To 1500
    For colIndex = 2 To 2 ' why is there a loop here?
        Select Case Cells(rwIndex, colIndex).Value
            Case Str1, Str2, Str3, Str4, Str5, Str6, Str7
                Cells(rwIndex, colIndex).EntireRow.Delete
            Case Else
                ' do nothing
        End Select
    Next colIndex
Next rwIndex
End Sub

if you prefer.