2 dimension array

[mwperkins]

Hi,

I am building a tool that contains a list of worksheets that the user can choose to either "Exclude" or resequence in their preferred order.

I am hoping to learn how this can be achieved using arrays and have so far developed the following:

Sub test_001()
Dim IndexCell As Range
Dim intCounter As Integer

'Data is stored in columns C and D, starting on row 9
'column D is a pre-populated unique list of sheet names
'column C is populated by a user selecting either "Exclude" or a number (i.e. the user is selecting which sheets they want to exclude and what sequence the sheets they need should be in)


intCounter = 0
    'Count how many 'rows' i need in the array -------START
    Set IndexCell = Range("C9")
    Do While Not IsEmpty(IndexCell)
        If IndexCell = "Exclude" Then
        'do nothing (I don't need to count sheets I will exclude)
        Else
            intCounter = intCounter + 1
        End If
        Set IndexCell = IndexCell.Offset(1, 0)
    Loop
    'Count how many 'rows' i need in the array -------END
    
    
    'Build the array --------------------------------------START
    ' Declare a two dimensional array
    ReDim arrSheetsToKeep(0 To (intCounter - 1), 0 To 1) As String
    intCounter = 0
    
    
    Set IndexCell = Range("C9")
    Do While Not IsEmpty(IndexCell)
        If IndexCell = "Exclude" Then
        'do nothing (I don't need to count sheets I will exclude)
        Else
            arrSheetsToKeep(intCounter, 0) = CStr(IndexCell.Value)
            arrSheetsToKeep(intCounter, 1) = CStr(IndexCell.Offset(0, 1).Value)
            intCounter = intCounter + 1
        End If
        Set IndexCell = IndexCell.Offset(1, 0)
    Loop
    'Build the array --------------------------------------END
    
    
    
    
    'Write the array back to cells (so that i can see if it worked)
    Range(Cells(9, 8), Cells(9 + intCounter - 1, 9)).Value = arrSheetsToKeep
    
    
    ' Next, I need to sort the array so that I know what needs to be sheet 1, sheet 2 etc.
    
    ' Then, I need to know how to loop through all the worksheets in the activebook and either hide them if they are NOT in the array, or read the required sheet position from the array if they ARE
    
End Sub

Are there better ways of achieving what i have done so far?
Can anyone help with sorting the array or testing if a value exists in it?


Thank you,
Mark

 

[Fluff]

How about

Sub mwperkins()
   Dim Ary As Variant, Tmp1 As Variant, Tmp2 As Variant
   Dim i As Long, j As Long
   
   Ary = Range("C9", Range("D" & Rows.Count).End(xlUp)).Value2
   For i = 1 To UBound(Ary)
      For j = i To UBound(Ary)
         If Ary(i, 1) > Ary(j, 1) Then
            Tmp1 = Ary(i, 1): Tmp2 = Ary(i, 2)
            Ary(i, 1) = Ary(j, 1): Ary(i, 2) = Ary(j, 2)
            Ary(j, 1) = Tmp1: Ary(j, 2) = Tmp2
         End If
      Next j
   Next i
   For i = 1 To UBound(Ary)
      If Evaluate("isref('" & Ary(i, 2) & "'!A1)") Then
         If LCase(Ary(i, 1)) = "exclude" Then
            Sheets(Ary(i, 2)).Visible = xlSheetHidden
         ElseIf Ary(i, 1) <> "" Then
            Sheets(Ary(i, 2)).Visible = xlSheetVisible
            Sheets(Ary(i, 2)).move Sheets(Ary(i, 1))
         Else
            Sheets(Ary(i, 2)).move Sheets(Sheets.Count)
         End If
      End If
   Next i
End Sub

 

[mwperkins]

Fluff,
Many thanks

Am I right in understanding that you replaced almost all my code with a single line: Ary = Range("C9", Range("D" & Rows.Count).End(xlUp)).Value2 ?

Am I right in understanding that the following is sorting the array:
For i = 1 To UBound(Ary)
For j = i To UBound(Ary)
If Ary(i, 1) > Ary(j, 1) Then
Tmp1 = Ary(i, 1): Tmp2 = Ary(i, 2)
Ary(i, 1) = Ary(j, 1): Ary(i, 2) = Ary(j, 2)
Ary(j, 1) = Tmp1: Ary(j, 2) = Tmp2
End If
Next j
Next i

And finally, what does If Evaluate("isref('" & Ary(i, 2) & "'!A1)") Then do?

Many thanks,
Mark

 

[Fluff]

1) Possibly, I didn't actually look at your code, I just went by your description.
2) You are quite right
3) It's checking if the sheet exists within the workbook.

 

[mwperkins]

many thanks. you just made my day a whole load easier. thankyou

 

[Fluff]

You're welcome & thanks for the feedback