# How to use Case and Array to improve code?

#### Sprucy

##### Board Regular
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### Norie

##### Well-known Member
Sprucy

What do the other subs do?

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

#### Sprucy

##### Board Regular
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

##### MrExcel MVP
Hi Sprucy

Code:
``````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

##### Board Regular

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

##### Well-known Member
Sprucy

If you want to use Select Case try something like this.
Code:
``````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``````

Hi Norie,

Perfect!

Need I say more?

Thanks again,
Cheers,

Sprucy

Replies
1
Views
239
Replies
14
Views
164
Replies
9
Views
575
Replies
5
Views
2K
Replies
6
Views
357

1,141,095
Messages
5,704,311
Members
421,338
Latest member
Pepess

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back