Clearing empties from a 2d array

Lavina

Board Regular
Joined
Dec 18, 2018
Messages
75
Hey guys,

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

Code:
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?
 
Last edited:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
Depending on how you are populating the array & what your criteria are, you could use something like
Code:
   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)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
ABCDEFGHI
1CodeValues
2a123456
3b1011
4c101102103
5d12345678
6e99989796
7fa1b2c3d4e5
8gAndyBeatriceCalDenice
9h1/2/20192/3/2019
10ixyz
11j-11.23elephantExcel 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.

Code:
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:

Code:
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!
 
Upvote 0
I tend to use a dictionary of dictionaries for dependant comboboxes.
With data like


Book1
HI
1CountyDistrict
2NorfolkKing's Lynn and West Norfolk
3East Riding of YorkshireKingston upon Hull, City of
4East Riding of YorkshireKingston upon Hull, City of
5CambridgeshireFenland
6KentShepway
7DorsetPoole
8Greater LondonGreenwich
9Greater LondonGreenwich
10Greater LondonBexley
11Greater LondonLewisham
12DevonTeignbridge
13DorsetPoole
14East SussexEastbourne
15East SussexWealden
16East SussexWealden
17South YorkshireDoncaster
18Greater LondonNewham
19Greater LondonNewham
20Greater LondonNewham
Pcode


And the code
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
 
Upvote 0

Forum statistics

Threads
1,223,959
Messages
6,175,649
Members
452,663
Latest member
MEMEH

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