Storing rows, columns and sheets in dynamic arrays

Question

I have been told there is a way of storing data in dynamic arrays which can then be referred to in a function.

I come from my recent answered question:Showing hidden column in another sheet

I am trying to find out how to store in a dynamic array which row (first array), column (second array) and sheet (third array) my code has to make action on. I still haven't done arrays in my class, I'm just guessing it is a dynamic one for what I have researched. Do you think this could be done in the same array using different dimensions? (Edited ->) -- Being more specific: I am looking for a way to store a number of specific rows(or columns) of a specific sheet to then (in a loop I guess) run my function for each element.

I have read the basic documentation (https://msdn.microsoft.com/en-us/library/aa716275(v=vs.60).aspx) and looked for similar posts here but I can't find it. If someone can point out what I should do or read to achieve this I can try and do it myself if you believe it's better.

Edited: I think I am doing some progress, but I still don't know how to refer to a the sheet within the range.

Would this do the job (if I didn't need sheet referring)?

Public Sub Test()
Dim Rng As Range
    Dim Area As Range
    Set Rng = Range("A1:A10,B1:B20,G1:G3")
    For Each Area In Rng.Areas
        Area.Hidden = True
    Next Area
End Sub

Answer 1

You can manage that with a single array of Range because the range refer to:

• The sheet
• The row

• The Column

  Dim array() as Range
  ...
  ' Store with
  set array(i) = worksheet.Range(a, b)
  ...
  ' Read with
  set range = array(i)
  

The link to msdn in your question explain how to manage Dynamic Arrays

update

The problem in your code is you not refer the worksheet you want. If no worksheet is indicate, in the best case an error is thrown, in the worst case it takes the "Activesheet" (yes, an error is a better case then work on you don't know what).

Consider you know the name of the sheet (or the position of it), you can pass it in parameters

Public Sub Test(byval sheetname as string)
    ' First solution: declare a worksheet variable referencing to your worksheet
    dim ws as worksheet, rng as range, area as range
    set ws = Worksheets(sheetname)
    Set rng = ws.Range("A1:A10,B1:B20,G1:G3")
    For Each area In rng.Areas
        area.Hidden = True
    Next Area

    ' You could replace the dim of ws by a With Worksheets(sheetname) 
    ' and work with .Range() instead
End Sub