Deleting data and element in a 1D array

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

I have a 1-D VBA array with 600,000 elements.

For X = 1 To MAX 'Max = 600,000
NumArray(X) = X
Next X

I then go into the array and randomly delete various data in th
elements. Lets say every 3rd element. I would write looping cod
around something like NumArray(3) = "", NumArray(6) = "" etc...

I want to be able to redimension the array so that all the element
with deleted data ("") are removed. So now the array size is onl
400,000. How do I this?

Thank-yo
 
Hi
one way:
use a second array and insert only the relevant items into this second
array
 
Just as a matter of Procedure: You cannot intermittently remove elements of an array to resize it
You can either expand its upper limit or reduce it: but from top down losing elements 600,000, 599,999..

So Franks suggestion is your only option

Dim Array2( ), i as Single, j as Singl
For i = 1 to Ubound(NumArray) '<< I don't know what your option base is
If Not NumArray(i) ="" The
j = j +1 '<< put this here if Option base is 1, but not below
Redim Preserve Array2(j
Array2(j) = NumArray(i
j = j +1 '<< put this here if Option base is 0, but not at begining
End If
Next
----- ExcelMonkey > wrote: ----

I have a 1-D VBA array with 600,000 elements

For X = 1 To MAX 'Max = 600,00
NumArray(X) =
Next

I then go into the array and randomly delete various data in th
elements. Lets say every 3rd element. I would write looping cod
around something like NumArray(3) = "", NumArray(6) = "" etc..

I want to be able to redimension the array so that all the element
with deleted data ("") are removed. So now the array size is onl
400,000. How do I this?

Thank-yo
 
There are a couple of other ways

1. (I am not recommending this), is to shuffle the data down. So move
4-UBound down by 1, and the repeat on the next iteration, or even be smarter
and move 4 & 5 down 1, 7 & 8 down 2, etc.

2. Copy the array to a worksheet range, delete every third item in that
range (remembering to do it backwards), reload the array. You don't even
have to ReDim it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

chris said:
Just as a matter of Procedure: You cannot intermittently remove elements of an array to resize it.
You can either expand its upper limit or reduce it: but from top down
Click to expand...
losing elements 600,000, 599,999...
 
Bob,
2. Copy the array to a worksheet range, delete every third item in that
range (remembering to do it backwards), reload the array. You don't even
have to ReDim it.
Click to expand...

Which Excel version, 2009 <g>?

Anders Silven
 
If one is willing to work with strings, than it might be possible.
There is still a "feature" missing in that it will not work with blank/null
strings. That is why you have to replace values with something unique
instead of a blank string "".

Sub Demo()
'// Dana DeLouis
Dim x As Long
Dim v As Variant
Const Unique = "ÿ" ' Alt + 0255
ReDim v(1 To 20)

For x = 1 To 20
v(x) = CStr(x)
Next x

'Delete every third
For x = 3 To 20 Step 3
v(x) = Unique
Next x
v = Filter(v, Unique, False)
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


chris said:
Just as a matter of Procedure: You cannot intermittently remove elements of an array to resize it.
You can either expand its upper limit or reduce it: but from top down
Click to expand...
losing elements 600,000, 599,999...
 
Whoops, missed the size of the array.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
With the 20's replaced by 600000, the size of the OP's array, it took 39
seconds to run on my machine. I know I don't always bow to efficiency,
but that's a lot even for me :-)

Alan Beban
 
Hi Alan,

On the slowest machine that I could conveniently find, I ran Dana's routine
(amended for the OP's 600k array) in slightly over 3.2 seconds - well under
one tenth of the time you report!
 
Hi Norman,

Because of the huge discrepancy I tried it again. It took 1 second to
run it for a 40,000 element array; 2 seconds for 80,000; 4 or 5 seconds
for 200,000; and 10 or 11 seconds for 300,000.

It would be interesting to see how fast it runs on Dana DeLouis's machine.

Alan Beban
 
Hi Alan. My times are just a little slower than Norman's. About 4.1
Seconds.

====================
Start of: Filter: 600000
Execution Time: 4.1122492 Sec. (~0.07 Minutes)
====================

Sub Demo()
'// Dana DeLouis
Const N As Long = 600000

TimerStart "Filter: " & N

Dim x As Long
Dim v As Variant
Const Unique = "ÿ" ' Alt + 0255
ReDim v(1 To N)

For x = 1 To N
v(x) = CStr(x)
Next x

'Delete every third
For x = 3 To N Step 3
v(x) = Unique
Next x
v = Filter(v, Unique, False)
TimerEnd
End Sub


====================
Start of: Filter: 300000
Execution Time: 1.8712951 Sec. (~0.03 Minutes)
====================
 
Just to mention, one can also enter just numbers like this...

For x = 1 To N
v(x) = x
Next x

instead of
v(x) = CStr(x)
Click to expand...

I usually find that it is a little faster to go ahead and enter numbers, and
let the function do the string conversion.

Using v(x) = x

====================
Start of: Filter: 600000
Execution Time: 3.8485017 Sec. (~0.06 Minutes)
====================

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

<snip>
 
Back
Top