How to use Case and Array to improve code?

[Sprucy]

Hi all,

My code below works fine, but can't seem to expend on it...

I currently use 9 macros (Del_A / Call Del_B... / Call Del_I ) to achieve what I want.
I tried adding a Array("A", "B", "C".... "I") and tried the Case If, but my VB knowledge isn't great, so I need some advice.

A) Is it worth changing my code?
B) If so, any ideas, and how?



---------------------------------------
Sub Del_A()

Dim qk As Long

For qk = Cells(100, 1).End(xlUp).Row To 1 Step -1

If Cells(qk, 1) > 100 And Cells(qk, 2) = "A" Then Rows(qk).Delete

Next

'Call Del_B

End Sub
--------------------------------------



Cheers,

Sprucy

 

[Norie]

Sprucy

What do the other subs do?

Is it just a case of looking for A, B, C etc?

 

[Sprucy]

Hi Norie,

The other Subs are identical except for this line:

If Cells(qk, 1) > 100 And Cells(qk, 2) ="A" Then Rows(qk).Delete

So:
Sub Del_A deletes all row if Column A is < 100 and Column B = A
Sub Del_B deletes all row if Column A is < 100 and Column B = B
Sub Del_C deletes all row if Column A is < 100 and Column B = C
And so on...

Cheers,

 

[pgc01]

Hi Sprucy

Please try:

Sub Del_A_To_I()
Dim qk As Long

For qk = Cells(100, "A").End(xlUp).Row To 1 Step -1

    If Cells(qk, "A") > 100 And Len(Cells(qk, "B")) = 1 And _
        InStr("ABCDEFGHI", Cells(qk, "B")) Then Rows(qk).Delete

Next
End Sub

Hope this helps
PGC

 

[Sprucy]

Hi PGC01,

A to I was to simplify the problem, I guess I mad things worst.

In reality I'm looking for 9 different words, all constant such as:
RED, BLUE, + 7 more.

Thanks anyway, I should of specified this in the first place, hope you haven't spent too much time on this.

Cheers,

 

[Norie]

Sprucy

If you want to use Select Case try something like this.

Sub Delete()
Dim qk As Long

For qk = Cells(100, "A").End(xlUp).Row To 1 Step -1

    If Cells(qk, "A") > 100 Then
        Select Case Cells(qk, "B")
              Case "RED", "BLUE" ' add other criteria
                  Rows(qk).Delete
              Case Else
                  ' do nothing
        End Select
    End If
Next
End Sub

 

[Sprucy]

Hi Norie,

Perfect!

Need I say more?

Thanks again,
Cheers,

Sprucy