Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case
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 "jlclyde" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case
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 "Mike H" wrote: 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 "jlclyde" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case
On Jan 6, 1:41*pm, Mike H wrote:
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case
On Jan 6, 2:00*pm, Mike H wrote:
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 "Mike H" wrote: 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 "jlclyde" wrote: 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- Hide quoted text - - 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select Case
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 "jlclyde" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select case | Excel Discussion (Misc queries) | |||
Case Select | Excel Worksheet Functions | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Select Case | Excel Discussion (Misc queries) | |||
Need help on Select Case | Excel Worksheet Functions |