Help with: Range Array SelectCase Loop delete: Array All Sheet.Names

[dlow]

Hi,

I have been trying to get this done for literally 12hrs. I am trying to learn how to use Arrays. I figured I would start with something small like a range & use the values in that range to delete some workSheets. lol I crashed myExcelApp. GoodTimes

Onto the matter @ hand would someone please be so kind as to help me out with either/or, but both would be great.

#1 Here I have some code that creates an Array from a range.
Array:

Please Login or Register  to view this content.

I would like to use the above Array to compare against SheetName in the CaseStatement below:

Please Login or Register  to view this content.

#2 I would also like to know how I could wrap all of a Workbooks Worksheets in an Array, so that I could use the above CaseStatment to delete matching sheets of another workbook.

I had some broilerPlate I was playing with a few hours ago before I crashedExcel; I cant find it but I did find something else I think I might be able to do that with.

Please Login or Register  to view this content.

As always any help is greatly appreciated. TIA

[Kaper]

- in case statement - you cannot directly use array
so either

Please Login or Register  to view this content.

Please Login or Register  to view this content.

but of course you can check array elements (not with case) like:

Please Login or Register  to view this content.

or just using sheets in array:

Please Login or Register  to view this content.

[dlow]

Dzień dobry Kaper,

- in case statement - you cannot directly use array

Thank you for telling me. I was driving myself crazy to find an example code snippet.

but of course you can check array elements (not with case) like:

I just got done testing the 1st procedure it work wonderfuly & I see what you did.

Please Login or Register  to view this content.

So cool the matchFunction [+1 nativeExcelFunction], Im gonna try to read-up on what your doing, so I can understand it better.

'+--------------------- Many Hours later -------------------------------

Sorry bout that its Saturday; Trying to mix work & play Im sure everbody else is too.

I finally got to test the other procedure & it works great, but there is 1 thing that is puzzling. I seem to be +1 either on the loop or the Array. VBA threw a Run-Time error'9': Subscript out of range. I just changed the Range to be -1 that allowed me to sidestep the Error.
If I understand the problem correctly or @ least assume I do, & I probably dont, [ Im so new with arrays ] anyway. There could be More Values in my Range than there are Sheets & visa-versa. If I am recalling what I read last night that we shouldnt go above/greater than the UBound.
When I stepped throu.. I see that the LB is 0 & when I get to the end Im +1. Which has me thinking would this be something we would use Redim for. Im gonna try it, but wanted to update this post with my findings. Just encase Im way off like with the SelectCase thing. Lastly I didnt want to delay any longer; my expression of sincere gratitude. Thanks for helping me put some new bait in my tackleBox

Please Login or Register  to view this content.

[dlow]

Banging on the keyBoard I came up with this:

Please Login or Register  to view this content.

I dont know if that "-1" is what should have been done, or if Im cheating & just lucky. I tried to do the Redim thing, but I dont think it belongs here. I dunno? #shrugg#

[dlow]

so either
Select Case sht.Name
Case Range("A1"), Range("A2"), Range("A3") '<---- I so... want this to be my Array[Range]

Thanks for that too. I just realize I could do: Case [A1], [A2], [A3],..... duh.... geez, man youRock thx.

[Kaper]

Yest in a number of situations you can treat as a "full equivalent"

Please Login or Register  to view this content.

As for UBound(arr) - 1

May be there was no sheet name in A100 during your tests?
Try such approach:

Please Login or Register  to view this content.

[dlow]

Yup, that was it there was no SheetX. I wasnt thinking about what I was doing, or trying to do. To further complicate things I was sure to have/had 2 WorkBooks with names that started the same & looked exactly alike, but with a different datasets.

These Arrays are blazing fast. I did a little test against the SelectCase Statement with the full equivalents, & its a Joke. The Arrays are 3x faster if not more; I have to get a Nano counter. The CaseStatement takes 3 secs to delete about 160 sheets.

Anyway thank you so much for helping me out, & getting me started with Arrays.

This should keep me out of trouble for a while.[or GetMe in it sooner, but either way Im having fun again. Now that Im not stuck & I have a couple new sports cars in the driveway iYKwIM].

All the best,