Select Case

[jlclyde]

I am trying to use Select Case. Sheet4.Range("C4") = 103, 113, 123,
220 and so on. I am trying to look up all of the i's in Rng and
determine if any of them are = to Sheet4.Range("C4"). this is the
code I have. It does not work as is.

Thanks,
Jay

For Each i In Rng
Select Case i
Case Sheet4.Range("C4")
i.EntireRow.Delete
End Select
Next i

 

[Mike H]

Hi,

A couple of points. You haven't defined Rng and sheet4.range is incorrect
syntax.
The solution below used Sheets(4) which is the fourth worksheet in the
workbook irrespective of name. But you could use

Sheets("Sheet4").Range("C4")

for the worksheet called Sheet4


Sub sonic()
Set Rng = Range("A1:A10")
For Each i In Rng
Select Case i
Case Is = Sheets(4).Range("C4")
i.EntireRow.Delete
End Select
Next i

End Sub


Mike

 

[Mike H]

One more point, unless you really want to do it with select case there's a
simpler way. Looping through the same range of A1 - A10 backwards you could
do this

For i = 10 To 1 Step -1
If Cells(i, 1).Value = Sheets("Sheet4").Range("C4").Value Then
Cells(i, 1).EntireRow.Delete
End If
Next i

Mike

 

[jlclyde]

Hi,

A couple of points. You haven't defined Rng and sheet4.range is incorrect
syntax.
The solution below used Sheets(4) which is the fourth worksheet in the
workbook irrespective of name.  But you could use

Sheets("Sheet4").Range("C4")

for the worksheet called Sheet4

Sub sonic()
Set Rng = Range("A1:A10")
For Each i In Rng
    Select Case i
        Case Is = Sheets(4).Range("C4")
            i.EntireRow.Delete
    End Select
Next i

End Sub

Mike

Mike, thanks for the points. Rng is defined earlier in the code. I
only included what i thought would help someone understand shat I
needed help with. I was also trying to use Sheet4 and not a sheet
named 4 or Sheet 4. What you suggested still does not work. I will
try to explain a little differently.

I have a Select Case i. I want to see if i = any of the values in C4
on the other sheet. All of the vlaues are in C4. It is inserted as
text to accomodate all teh numbers.

Or is there another way to include multiple numbers as one case in
excel? Maybe from a range?

Thanks,
Jay

 

[jlclyde]

One more point, unless you really want to do it with select case there's a
simpler way. Looping through the same range of A1 - A10 backwards you could
do this

For i = 10 To 1 Step -1
    If Cells(i, 1).Value = Sheets("Sheet4").Range("C4").Value Then
           Cells(i, 1).EntireRow.Delete
    End If
Next i

Mike








- Show quoted text -

I like this answer to simplify things. But still C4 is not a number,
it is a series of numbers. What can be done about this?
Jay

 

[Shane Devenshire]

Hi,

Actually, Sheet4.Range("C4") is legal syntax, it means the code name for the
sheet is Sheet4.

If you are trying to delete all rows in which an entry in a single column
equals the entry in C4 then you can do this very fast using the following
code:

Sub DeleteMatches()
Application.ScreenUpdating = False
Columns("A:A").Insert
Range("A1:A" & [B65536].End(xlUp).Row).Select
Selection = "=IF(RC[1]=R4C4,1,""N"")"
Selection.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
Columns("A:A").Delete
End Sub

In this example it is assumed that the range where the items you want to
find are located in column B

If this helps, please click the Yes button.

cheers,
Shane Devenshire