vba help - select case from range

[Mallesh23]

Hi Team,

Below code helps for deleting of rows. need help in making select case dynamic.

Case "Dhoni", "Sachin", "Sehwag" '<- Keep these , this is hard coded how to pass these criteria from range /or variable here.
like Case arr_player ' not working getting type mismatch.[/CODE]

arr_player = WorksheetFunction.Transpose(Range("D2:d4").Value)

Sub Del_Except()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long

Dim arr_player As Variant
arr_player = WorksheetFunction.Transpose(Range("D2:d4").Value)

  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    Select Case (a(i, 1))
      Case "Dhoni", "Sachin", "Sehwag"   '<- Keep these  , this is hard coded, how to pass variable here
      Case arr_player  ' not working getting type mismatch.

Case Else '<- Delete everything else
b(i, 1) = 1
k = k + 1
End Select
Next i
If k > 0 Then
Application.ScreenUpdating = False
With Range("A2").Resize(UBound(a), nc)
.Columns(nc).Value = b
.Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
.Resize(k).EntireRow.Delete
End With
Application.ScreenUpdating = True
End If
End Sub[/CODE]


Table with Criteria list.

Book1
	A	B	C	D
1	Name			Criteria
2	Sehwag			Sachin
3	Sehwag			Dhoni
4	Kohli			Sehwag
5	sachin
6	sachin
7	Gayle
8	Dhoni
9	Gayle
10	Sehwag
11	Gayle
12	Dhoni
13	Sehwag
14	Ponting
15	Dhoni
16	Ponting
17	sachin
18	Sehwag
19	Dhoni
20	Kohli
21	Kohli
22	sachin
23	Sehwag
24	sachin
25	Sehwag
26	Kohli
27	Kohli
28	Sehwag
29	Sehwag
30	Kohli
31	Dhoni
Sheet1

Thanks
mg

 

[Norie]

Don't use Select Case.

Read the values to keep/delete into an array and then use Application.Match to check the values from the sheet against the values in the array and take the appropriate acion.