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:

Sub SimpleArray()
'You can use code like the following to loop through the array of the worksheet values:
'http://www.cpearson.com/excel/ArraysAndRanges.aspx
Dim Arr() As Variant
Arr = Range("A20:A100")
Dim R As Long
Dim C As Long
For R = 1 To UBound(Arr, 1) ' First array dimension is rows.
    For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.
        Debug.Print Arr(R, C)
    Next C
Next R

End Sub

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

Sub iCase_AWK_DelShs()
'http://www.vbaexpress.com/forum/showthread.php?14285-Solved-delete-all-sheets-not-in-array%281-to-4%29

'+-----------
 Dim sht As Worksheet
Application.DisplayAlerts = False
For Each sht In ActiveWorkbook.Worksheets
Select Case sht.Name
Case Is = "SheetNamesArray"   '<---- I so... want this to be my Array[Range]
Debug.Print sht.Name '.Delete
Case Else
'Do nothing
End Select
Next sht
Application.DisplayAlerts = True
'+-----------
End Sub

#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.

Sub iArry()
    Dim SNarray, i
    ReDim SNarray(1 To Sheets.Count)
    For i = 1 To Sheets.Count
        SNarray(i) = ActiveWorkbook.Sheets(i).Name
Debug.Print SNarray(i)
    Next
End Sub

As always any help is greatly appreciated. TIA

[Kaper]

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

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

Select Case sht.Name
Case "Sheet1", "Sheet2"  '<---- I so... want this to be my Array[Range]

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

Sub iCase_AWK_DelShs_2()
Dim sht As Worksheet, arr()
Application.DisplayAlerts = False
arr() = Range("A1:A2").Value
For Each sht In ActiveWorkbook.Worksheets
  If Not IsError(Application.Match(sht.Name, arr, 0)) Then
    Debug.Print sht.Name '.Delete
  Else
  ' do nothing or something else
  End If
Next sht
Application.DisplayAlerts = True
End Sub

or just using sheets in array:

Sub iCase_AWK_DelShs3()
Dim arr(), i As Integer
Application.DisplayAlerts = False
arr() = Range("A1:A2").Value
For i = LBound(arr) To UBound(arr)
  Debug.Print Sheets(i).Name '.Delete
Next i
Application.DisplayAlerts = True
End Sub

[dlow]

Banging on the keyBoard I came up with this:

Sub Kaper_A_WBkShs_Array()
Dim myRng As Range: Set myRng = Workbooks("iTestStuff.xlsb").Sheets("iTestArray").Range("A2:A100").Value
Dim arr() As Variant: arr() = myRng.Value
Dim i As Long
Application.DisplayAlerts = False
For i = LBound(arr) To UBound(arr) - 1
Debug.Print Sheets(i).Name '.Delete
Next i
Application.DisplayAlerts = True
End Sub

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.

[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.

Sub KaperArray_A_WBk_Shs() 'CheckArrayElements 
'http://www.excelforum.com/excel-programming-vba-macros/1070696-help-with-range-array-selectcase-loop-delete-array-all-sheet-names.html#post4008983

Dim sht As Worksheet, arr()
Application.DisplayAlerts = False
arr() = Workbooks("iTestStuff.xlsb").Sheets("iTestArray").Range("A2:A100").Value
For Each sht In ActiveWorkbook.Worksheets
  If Not IsError(Application.Match(sht.Name, arr, 0)) Then
    sht.Delete 'Debug.Print
  Else
  ' do nothing or something else 'clever
  End If
Next sht

End Sub

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

Sub Kaper_A_WBkShs_Array()
Dim arr(), i As Integer
Application.DisplayAlerts = False
arr() = Workbooks("iTestStuff.xlsb").Sheets("iTestArray").Range("A2:A100").Value
For i = LBound(arr) To UBound(arr)
  Debug.Print Sheets(i).Name '.Delete
Next i
Application.DisplayAlerts = True
End Sub

[Kaper]

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

cells(1,"A")
cells(1,1)
Range("A1")
[A1]

As for UBound(arr) - 1

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

With Workbooks("iTestStuff.xlsb").Sheets("iTestArray")
  Set myRng = .range(.range("A2"),.range("A2").end(xldown))
end with

[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,