Deleting data and element in a 1D array

[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

 

[Frank Kabel]

Hi
one way:
use a second array and insert only the relevant items into this second
array

 

[Guest]

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

 

[Bob Phillips]

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)

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...

 

[Anders S]

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.

Which Excel version, 2009 <g>?

Anders Silven

 

[Dana DeLouis]

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
= = = = = = = = = = = = = = = = =

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...

 

[Bob Phillips]

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)

 

[Alan Beban]

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

 

[Norman Jones]

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!

 

[Alan Beban]

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

 

[Dana DeLouis]

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)
====================

 

[Dana DeLouis]

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)

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>