Case Statement Question

[chirayu]

Hi All,

This is with regards to the below Case Statement

Select Case ActiveCell.Value
Case "Site1", "Site3", "Site9"
Rows(ActiveCell.Row).Select
Selection.Delete
ActiveCell.Offset(0, 1).Select
Case Else
ActiveCell.Offset(1, 0).Select
End Select
Loop

Currently under the case argument I have specified my values i.e. Site1/Site3/Site9.

Is it possible instead to use a given range in the macro so that I don't need to keep manually typing in my values in the macro itself whenever change is needed? e.g.

Worksheets("Sheet1").Range("A1:A3").Value

 

[wudixin96]

Dim findRange As Range, rng As Range
    Set findRange = Worksheets("Sheet1").Range("A1:A3")
   
    Set rng = findRange.Find(ActiveCell.Value, LookAt:=xlWhole)
    If Not rng Is Nothing Then
        Rows(ActiveCell.Row).Select
        Selection.Delete
        ActiveCell.Offset(0, 1).Select
    Else
        ActiveCell.Offset(1, 0).Select
    End If

 

[shrivallabha]

If there are multiple actions and combinations then usage of Select is good. In above case, If...else construct would also do fine.

Select Case IsNumeric(Application.Match(ActiveCell.Value, Sheets("Sheet1").Range("A1:A3").Value, 0))
Case True
  Rows(ActiveCell.Row).Select
  Selection.Delete
  ActiveCell.Offset(0, 1).Select
Case Else
  ActiveCell.Offset(1, 0).Select
End Select

 

[chirayu]

Thanks
@wudixin96
@shrivallabha