How to use Case and Array to improve code?

Sprucy

Board Regular
Joined
Oct 31, 2005
Messages
92
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
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Sprucy

What do the other subs do?

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

Sprucy

Board Regular
Joined
Oct 31, 2005
Messages
92
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
Joined
Apr 25, 2006
Messages
19,884
Hi Sprucy

Please try:

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
Joined
Oct 31, 2005
Messages
92

ADVERTISEMENT

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
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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
 

Sprucy

Board Regular
Joined
Oct 31, 2005
Messages
92
Hi Norie,

Perfect!

Need I say more?

Thanks again,
Cheers,

Sprucy
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top