mwperkins

Board Regular
Joined
Oct 29, 2002
Messages
156
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:
Code:
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
 
Last edited by a moderator:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
How about
Code:
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
 
Upvote 0
Solution
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
 
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top