#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 410
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 410
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 410
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 857
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select case Hein Excel Discussion (Misc queries) 2 October 22nd 08 07:06 AM
Case Select NoodNutt Excel Worksheet Functions 7 September 21st 08 02:10 AM
Case without Select Case error problem Ayo Excel Discussion (Misc queries) 2 May 16th 08 03:48 PM
Select Case Jeff Excel Discussion (Misc queries) 1 February 27th 06 03:56 PM
Need help on Select Case Susan Hayes Excel Worksheet Functions 1 November 3rd 04 11:25 PM


All times are GMT +1. The time now is 10:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"