Till now, your code is pretty much clean and easy to read, except for getting the upper and lower bounds, but using it like here it could be simplyfied (at least for my eye) by changing AssignArrayBounds like this:
Till now, your code is pretty much clean and easy to read, except for getting the upper and lower bounds, but using it like here it could be simplyfied (at least for my eye) by changing AssignArrayBounds like this:
While knowing you are going to use Find for this range (which always just checks inside the UsedRange) it s. Find is still the best way to improve the calculation-time by a big amount
While knowing you are going to use Find for this range (which always just checks inside the UsedRange) it s still the best way to improve the calculation-time by a big amount
While knowing you are going to use Find for this range (which always just checks inside the UsedRange). Find is still the best way to improve the calculation-time by a big amount
You call it to redimReDim the second array and then looping through all items. But having both as ByRef ... As Variant I don't know why you not simply useeuse:
This 2 subs will output ~ the same time. The bigger the range gets the more comp1 will speed out comp2. At a range of 3 cells there is allreadyalready a noteablenoticeable difference. Having a range of 100 cells and 10000 cycles comp1 will probably just print a 0 while comp 2 takes some seconds. Also take into account that Cells(1, 1) will be ~66% faster than Range("A1") and normally there will be multiple checks and not only 1 get, so the more you interact with the sheet, the faster you will get by using a variable. Feel free to play a bit with this subs.
Hint: never use something like [A1]... while it may look cool it is just extremely slow...
allwaysalways set variant = Array()
Why use ElementsToStrings1dArray as function? Is somewhere a different use than arrHeadingsRow = ElementsToStrings1dArray(arrHeadingsRow)? That said Transpose2dArray would make sense to being a function having it be set Transpose2dArray = arrTransposedArray at the end. Also having the desired coding Array1 = Array2 at the end, there is no need to redimReDim Array1 (it will become a copy and auto ReDimmed to the ranges of Array2.
While it is more code now, it should also be much faster. Just running for the first visible row and then count all rows in range minus visible rows. It gets a bit confusing for the count of visible rows using the specialcellsSpecialCells. rng.SpecialCells(xlCellTypeVisible).Rows.Count will just count the rows from the first visible till the next hidden one. So i go with rng.Columns(1) and then count the cells.
Note: Would be happy if someone could check my spelling/formatting and then delete this note. Thanks
You call it to redim the second array and then looping through all items. But having both as ByRef ... As Variant I don't know why you not simply usee:
This 2 subs will output ~ the same time. The bigger the range gets the more comp1 will speed out comp2. At a range of 3 cells there is allready a noteable difference. Having a range of 100 cells and 10000 cycles comp1 will probably just print a 0 while comp 2 takes some seconds. Also take into account that Cells(1, 1) will be ~66% faster than Range("A1") and normally there will be multiple checks and not only 1 get, so the more you interact with the sheet, the faster you will get by using a variable. Feel free to play a bit with this subs.
Hint: never use something like [A1]... while it may look cool it is just extremely slow...
allways set variant = Array()
Why use ElementsToStrings1dArray as function? Is somewhere a different use than arrHeadingsRow = ElementsToStrings1dArray(arrHeadingsRow)? That said Transpose2dArray would make sense to being a function having it be set Transpose2dArray = arrTransposedArray at the end. Also having the desired coding Array1 = Array2 at the end, there is no need to redim Array1 (it will become a copy and auto ReDimmed to the ranges of Array2.
While it is more code now, it should also be much faster. Just running for the first visible row and then count all rows in range minus visible rows. It gets a bit confusing for the count of visible rows using the specialcells. rng.SpecialCells(xlCellTypeVisible).Rows.Count will just count the rows from the first visible till the next hidden one. So i go with rng.Columns(1) and then count the cells.
You call it to ReDim the second array and then looping through all items. But having both as ByRef ... As Variant I don't know why you not simply use:
This 2 subs will output ~ the same time. The bigger the range gets the more comp1 will speed out comp2. At a range of 3 cells there is already a noticeable difference. Having a range of 100 cells and 10000 cycles comp1 will probably just print a 0 while comp 2 takes some seconds. Also take into account that Cells(1, 1) will be ~66% faster than Range("A1") and normally there will be multiple checks and not only 1 get, so the more you interact with the sheet, the faster you will get by using a variable. Feel free to play a bit with this subs.
Hint: never use something like [A1]... while it may look cool it is just extremely slow...
always set variant = Array()
Why use ElementsToStrings1dArray as function? Is somewhere a different use than arrHeadingsRow = ElementsToStrings1dArray(arrHeadingsRow)? That said Transpose2dArray would make sense to being a function having it be set Transpose2dArray = arrTransposedArray at the end. Also having the desired coding Array1 = Array2 at the end, there is no need to ReDim Array1 (it will become a copy and auto ReDimmed to the ranges of Array2.
While it is more code now, it should also be much faster. Just running for the first visible row and then count all rows in range minus visible rows. It gets a bit confusing for the count of visible rows using the SpecialCells. rng.SpecialCells(xlCellTypeVisible).Rows.Count will just count the rows from the first visible till the next hidden one. So i go with rng.Columns(1) and then count the cells.
Note: Would be happy if someone could check my spelling/formatting and then delete this note. Thanks