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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany


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 the
elements. Lets say every 3rd element. I would write looping code
around something like NumArray(3) = "", NumArray(6) = "" etc...

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

Thank-you


---
Message posted from http://www.ExcelForum.com/

Just as a matter of Procedu 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


--
Message posted from http://www.ExcelForum.com

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" wrote in message
...
Just as a matter of Procedu 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 Single
For i = 1 to Ubound(NumArray) '<< I don't know what your option base is
If Not NumArray(i) ="" Then
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,000
NumArray(X) = X
Next X

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

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

Thank-you


---
Message posted from http://www.ExcelForum.com/

Bob,

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


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

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" wrote in message
...
Just as a matter of Procedu 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 Single
For i = 1 to Ubound(NumArray) '<< I don't know what your option base is
If Not NumArray(i) ="" Then
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,000
NumArray(X) = X
Next X

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

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

Thank-you

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)

"Anders S" wrote in message
...
Bob,

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


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

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

Dana DeLouis wrote:

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

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!

---
Regards,
Norman


"Alan Beban" wrote in message
...
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 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

Norman Jones wrote:

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!

---
Regards,
Norman


"Alan Beban" wrote in message
...

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


11 seconds for 300,000

====================
Start of: Filter: 300000
Execution Time: 1.8712951 Sec. (~0.03 Minutes)
====================

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


"Alan Beban" wrote in message
...
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

Norman Jones wrote:

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!

---
Regards,
Norman


"Alan Beban" wrote in message
...

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

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