Clearing empties from a 2d array

[Lavina]

Hey guys,

i have an array, once creating it i make it 2d:

ReDim  allPksWithEmpty(1 To lastRow, 1 To 2)

Since I'm not able to set the 1st dimension anymore, i cap it out at maximum rows in sheet.

Of course i only pick up a little data depending on some ifs, thus i end up with a big array size, while almost all of it is empty.

I then thought i could just remake the array depending if field is empty then ignore, but i still want to keep it 2d, and i cannot change 1st dimension then, thus it's not possible

Is my approach wrong or is the only way to remove empties is to look at specific dimensions 1 by 1?

 

[Norie]

You could try swapping the dimensions around when populating the array, then re-dimension the last dimension based on the no of rows and then transpose the array to swap the dimensions.

 

[Lavina]

You could try swapping the dimensions around when populating the array, then re-dimension the last dimension based on the no of rows and then transpose the array to swap the dimensions.

Hey Norie, thanks for your reply, your approach makes a lot of sense, I've already implemented it! But, what approach should i take if both of my dimension caps are unknown?

 

[Rick Rothstein]

Hey Norie, thanks for your reply, your approach makes a lot of sense, I've already implemented it! But, what approach should i take if both of my dimension caps are unknown?

If you are filling your array using loops, then maintain a counter for each dimension and increase whichever one by one each time you put an item in it. You can then resize the last dimension using the counter for it, then transpose the array and resize what was the first dimension but now is the second one using the counter for the first dimension, then finish off by transposing one more time to put the array back in its original configuration.

 

[Lavina]

Thank you for the clear explanation Rick! Never again will i have trouble like this!

 

[Fluff]

Depending on how you are populating the array & what your criteria are, you could use something like

   Dim x As Long, lastRow As Long
   
   x = Application.CountIfs(Range("H2:H" & lastRow), "Dorset", Range("B2:B" & lastRow), "Yes")
   ReDim Ary(1 To x, 1 To 2)

 

[Eric W]

You might also want to consider other data structures. If your second dimension is 1 to 2, then you could use a Scripting.Dictionary. There are many links available to read about them, but briefly you could add to one by doing this:

MyDict.Add a, 1

and you don't need to worry about defining the size. Other dictionary methods such as .Count, .Keys, .Items, etc. provide the ability to see how many items are in the dictionary, or to get a specific entry, or get all the keys/items at once, and so on.

If your second dimension is unknown, you can use a dictionary of dictionaries. There are other data structures available, such as Collections, ArrayList, SortedList, etc. Picking the right data structure for your particular need can vastly simplify your code, and make it run faster.

 

[Lavina]

You might also want to consider other data structures. If your second dimension is 1 to 2, then you could use a Scripting.Dictionary. There are many links available to read about them, but briefly you could add to one by doing this:

MyDict.Add a, 1

and you don't need to worry about defining the size. Other dictionary methods such as .Count, .Keys, .Items, etc. provide the ability to see how many items are in the dictionary, or to get a specific entry, or get all the keys/items at once, and so on.

If your second dimension is unknown, you can use a dictionary of dictionaries. There are other data structures available, such as Collections, ArrayList, SortedList, etc. Picking the right data structure for your particular need can vastly simplify your code, and make it run faster.

Hello Eric,

i am looking into your points and I'm embarrassed to say that I've only been using arrays for any problem i had. Dictionaries are awesome and would have saved me some nerves, will definitely make a better choice of what to use next time! Could you maybe expand on the idea of the dictionary of dictionaries? How is it implemented? What are the correct times of utilizing it?

 

[Eric W]

Well, I'm kind of regretting a little mentioning a dictionary of dictionaries. It is a valid tool, but there are usually better option. I can only remember once where I think it was a valid choice in one of my projects. However, the key point is a dictionary entry consists of a key and an item. The item can be anything, a number, a string, an array, an object, or a dictionary. So when creating a entry, make it a dictionary. Then you can reference that dictionary by first referencing the key of the main dictionary. As an example:

Book1
	A	B	C	D	E	F	G	H	I
1	Code	Values
2	a	1	2	3	4	5	6
3	b	10	11
4	c	101	102	103
5	d	1	2	3	4	5	6	7	8
6	e	99	98	97	96
7	f	a1	b2	c3	d4	e5
8	g	Andy	Beatrice	Cal	Denice
9	h	1/2/2019	2/3/2019
10	i	x	y	z
11	j	-1	1.23	elephant	Excel is fun
Sheet5

Consider that spreadsheet. Codes down the left, a variable number of items for each code. This macro will create a dictionary, add an entry for each code, with a dictionary as its item, then insert the values into the second dictionary. Then the last loop shows how to access the dictionaries and displays them transposed to the right.

Sub DofD()
Dim CodeDict As Object, r As Long, c As Long, MyKeys As Variant


    Set CodeDict = CreateObject("Scripting.Dictionary")
    
    For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        CodeDict.Add Cells(r, "A").Value, CreateObject("Scripting.Dictionary")
        For c = 2 To Cells(r, Columns.Count).End(xlToLeft).Column
            CodeDict(Cells(r, "A").Value).Add Cells(r, c).Value, 1
        Next c
    Next r
    
    MyKeys = CodeDict.keys
    For c = 1 To CodeDict.Count
        Cells(1, 12 + c) = MyKeys(c - 1)
        Cells(2, 12 + c).Resize(CodeDict(MyKeys(c - 1)).Count).Value = WorksheetFunction.Transpose(CodeDict(MyKeys(c - 1)).keys)
    Next c
            
End Sub

But, that's just to illustrate. You can do the same thing as the last macro, with this shorter and more efficient version:

Sub ArrayVersion()
Dim lr As Long, r As Long, lc As Long, mc As Long, MyArray As Variant


    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
    For r = 2 To lr
        lc = Cells(r, Columns.Count).End(xlToLeft).Column
        mc = IIf(lc > mc, lc, mc)
    Next r
    
    MyArray = Range(Cells(1, 1), Cells(lr, mc)).Value
    Range("M1").Resize(mc, lr).Value = WorksheetFunction.Transpose(MyArray)
            
End Sub

My overall point being that there are many types of data structures. If you're going to be programming for the long haul, you need to learn what they are, and learn how to choose the right one for your particular need.

Good luck!

 

[Fluff]

I tend to use a dictionary of dictionaries for dependant comboboxes.
With data like

Book1
	H	I
1	County	District
2	Norfolk	King's Lynn and West Norfolk
3	East Riding of Yorkshire	Kingston upon Hull, City of
4	East Riding of Yorkshire	Kingston upon Hull, City of
5	Cambridgeshire	Fenland
6	Kent	Shepway
7	Dorset	Poole
8	Greater London	Greenwich
9	Greater London	Greenwich
10	Greater London	Bexley
11	Greater London	Lewisham
12	Devon	Teignbridge
13	Dorset	Poole
14	East Sussex	Eastbourne
15	East Sussex	Wealden
16	East Sussex	Wealden
17	South Yorkshire	Doncaster
18	Greater London	Newham
19	Greater London	Newham
20	Greater London	Newham
Pcode

And the code

Option Explicit
Dim ufDic As Object
Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox2.List = ufDic(Me.ComboBox1.Value).Keys
End Sub

Private Sub UserForm_Initialize()
   Dim Cl As Range
   Set ufDic = CreateObject("scripting.dictionary")
   For Each Cl In Range("H2", Range("H" & Rows.Count).End(xlUp))
      If Not ufDic.Exists(Cl.Value) Then ufDic.add Cl.Value, CreateObject("scripting.dictionary")
      ufDic(Cl.Value)(Cl.Offset(, 1).Value) = Empty
   Next Cl
   Me.ComboBox1.List = ufDic.Keys
End Sub