Remove First Row from Variant Array FAST?
 

Suppose I have a 2D variant array, and I want to remove the first row
from it. Is there a really fast way to do that, rather manually copying
every value into a new variant array? Perhaps using API calls or
manipulating the underlying SAFEARRAY structure?

Also, a related question. Suppose I have a Double array with millions
of items in the array. Is there a quick way to chop it in half, perhaps
by altering the pointer to the first index and altering the number of
items in the SAFEARRAY?

I am not experienced with any of this stuff, but I thought it might be
possible. It would useful to me, if possible. Any help would be
appreciated.

Remove First Row from Variant Array FAST?
 

R Avery wrote:

. . . Suppose I have a Double array with millions
of items in the array. Is there a quick way to chop it in half . . . ?
The array has a top half, a bottom half, a right half and a left half.
What are you asking?

Alan Beban

Remove First Row from Variant Array FAST?
 

R Avery wrote:
Suppose I have a 2D variant array, and I want to remove the first row
from it. Is there a really fast way to do that, rather manually copying
every value into a new variant array? Perhaps using API calls or
manipulating the underlying SAFEARRAY structure?

Also, a related question. Suppose I have a Double array with millions
of items in the array. Is there a quick way to chop it in half, perhaps
by altering the pointer to the first index and altering the number of
items in the SAFEARRAY?

I am not experienced with any of this stuff, but I thought it might be
possible. It would useful to me, if possible. Any help would be
appreciated.
And what does "FAST" mean?

Alan Beban

Remove First Row from Variant Array FAST?
 

Sorry. Let's say it is a 1-D array, and I want to quickly change it to
contain less items, ideally without reallocating any memory at all.

Remove First Row from Variant Array FAST?
 

R Avery wrote:

Sorry. Let's say it is a 1-D array, and I want to quickly change it to
contain less items, ideally without reallocating any memory at all.
This is like pulling teeth. Do you want the left half or the right half?

Alan Beban

Remove First Row from Variant Array FAST?
 

R Avery wrote:

Sorry. Let's say it is a 1-D array, and I want to quickly change it to
contain less items, ideally without reallocating any memory at all.
Your original post in this thread said a 2-D array; now it's 1-D. I hope
noone wasting time on your original request.

Alan Beban

Remove First Row from Variant Array FAST?
 

In article ,
Alan Beban wrote:

This is like pulling teeth.

The technical term I learned in the Navy is playing "Bring Me a Rock":

Capt.: "Bring me a rock..."

Ens.: "Here's a nice rock!"

Capt.: "Wrong rock - this rock is grey. Bring me another rock..."

Ens.: "Here's a nice red rock!"

Capt.: "Wrong rock - this rock isn't smooth. Bring me another rock..."

....

Depending on the acuity of the Ensign, this can be played many, many
times for many, many rounds. Hours of enjoyment.

If not done strictly for training, eventually the Ensign learns to say
"get your own d**n rock" on about the 2nd iteration. However, I also
learned that when the Captain plays Bring Me a Rock, it's *always* for
training...<g

Remove First Row from Variant Array FAST?
 

Also, a related question. Suppose I have a Double array
with millions of items in the array. Is there a quick
way to chop it in half,

Looks like you're having a hard time getting your question
across.

Maybe you can find something half useful in following. In
a new workbook step through the code.

Sub SampleData()
With Worksheets(2)
..Name = "arrOrig"
..UsedRange.Clear
..[A1] = 1: .[B1].Formula = "=A1+0.2"
..Range("A1:B1").AutoFill .Range("A1:B20000")

'.[C1].Formula = "=A1+0.3": .[D1].Formula = "=A1+0.4"
'.Range("C1:D1").AutoFill .Range("C1:D20000")
End With
Worksheets(3).Name = "temp"
Worksheets(1).Name = "arrHalf"
End Sub

Sub SplitArray()
Dim wsHalf As Worksheet, wsTmp As Worksheet
Dim vArr, aCols As Long, aRows As Long
Dim R As Range

Set R = Worksheets("arrOrig").UsedRange
aCols = R.Columns.Count: aRows = R.Rows.Count
Set wsHalf = Worksheets("arrHalf")
Set wsTmp = Worksheets("temp")

wsHalf.UsedRange.Clear

'right half
ReDim vArr(1 To aRows, aCols) As Long
vArr = R.Value

wsTmp.Range("A1").Resize(UBound(vArr, 1), _
UBound(vArr, 2)).Value = vArr

ReDim vArr(1 To aRows, aCols / 2)

vArr = wsTmp.Range("A1").Offset(0, aCols / 2). _
Resize(UBound(vArr, 1), UBound(vArr, 2)).Value

wsTmp.UsedRange.Clear

wsHalf.Range("A1").Resize(UBound(vArr, 1), _
UBound(vArr, 2)).Value = vArr

wsHalf.Activate
Stop

'bottom half
wsHalf.UsedRange.Clear
ReDim vArr(1 To aRows, aCols)
vArr = R.Value

wsTmp.Range("A1").Resize(UBound(vArr, 1), _
UBound(vArr, 2)).Value = vArr

ReDim vArr(1 To aRows / 2, aCols)

vArr = wsTmp.Range("A1").Offset(aRows / 2, 0). _
Resize(aRows / 2, UBound(vArr, 2)).Value

wsTmp.UsedRange.Clear

wsHalf.Range("A1").Resize(UBound(vArr, 1), _
UBound(vArr, 2)).Value = vArr
End Sub

You will need some working space, might need to add a temp
sheet to some workbook.
Array size is limited to sheet dimensions. So not millions
of rows, I havn't tried with 10,000 x 100 items.
Array must be 2D, even if only one row or col of data.
Lbound must not be 0 (try Option base 1).

Should be able to re-dimension & re-populate your array
with any "rectangular area" within the original array.

Regards
Peter

-----Original Message-----
Suppose I have a 2D variant array, and I want to remove
the first row
from it. Is there a really fast way to do that, rather
manually copying
every value into a new variant array? Perhaps using API
calls or
manipulating the underlying SAFEARRAY structure?

Also, a related question. Suppose I have a Double array
with millions
of items in the array. Is there a quick way to chop it
in half, perhaps
by altering the pointer to the first index and altering
the number of
items in the SAFEARRAY?

I am not experienced with any of this stuff, but I
thought it might be
possible. It would useful to me, if possible. Any help
would be
appreciated.
.

Remove First Row from Variant Array FAST?
 

Depends on your definition of 2D variant array. If it consists of
multiple 1D arrays each in one element of a 1D array inside a variant,
yes, you can manipulate a particular dimension fast. If it is the more
traditional array of variants, no.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , ravery74
@yahoo.co.uk says...
Suppose I have a 2D variant array, and I want to remove the first row
from it. Is there a really fast way to do that, rather manually copying
every value into a new variant array? Perhaps using API calls or
manipulating the underlying SAFEARRAY structure?

Also, a related question. Suppose I have a Double array with millions
of items in the array. Is there a quick way to chop it in half, perhaps
by altering the pointer to the first index and altering the number of
items in the SAFEARRAY?

I am not experienced with any of this stuff, but I thought it might be
possible. It would useful to me, if possible. Any help would be
appreciated.

Remove First Row from Variant Array FAST?
 

This is an interesting idea, although you're right - it would not work
for an array with millions of rows because of the sheet row limitation.
Also, the writing and reading from ranges might make it slow.

Peter T wrote:
Also, a related question. Suppose I have a Double array
with millions of items in the array. Is there a quick
way to chop it in half,


Looks like you're having a hard time getting your question
across.

Maybe you can find something half useful in following. In
a new workbook step through the code.

Sub SampleData()
With Worksheets(2)
.Name = "arrOrig"
.UsedRange.Clear
.[A1] = 1: .[B1].Formula = "=A1+0.2"
.Range("A1:B1").AutoFill .Range("A1:B20000")

'.[C1].Formula = "=A1+0.3": .[D1].Formula = "=A1+0.4"
'.Range("C1:D1").AutoFill .Range("C1:D20000")
End With
Worksheets(3).Name = "temp"
Worksheets(1).Name = "arrHalf"
End Sub

Sub SplitArray()
Dim wsHalf As Worksheet, wsTmp As Worksheet
Dim vArr, aCols As Long, aRows As Long
Dim R As Range

Set R = Worksheets("arrOrig").UsedRange
aCols = R.Columns.Count: aRows = R.Rows.Count
Set wsHalf = Worksheets("arrHalf")
Set wsTmp = Worksheets("temp")

wsHalf.UsedRange.Clear

'right half
ReDim vArr(1 To aRows, aCols) As Long
vArr = R.Value

wsTmp.Range("A1").Resize(UBound(vArr, 1), _
UBound(vArr, 2)).Value = vArr

ReDim vArr(1 To aRows, aCols / 2)

vArr = wsTmp.Range("A1").Offset(0, aCols / 2). _
Resize(UBound(vArr, 1), UBound(vArr, 2)).Value

wsTmp.UsedRange.Clear

wsHalf.Range("A1").Resize(UBound(vArr, 1), _
UBound(vArr, 2)).Value = vArr

wsHalf.Activate
Stop

'bottom half
wsHalf.UsedRange.Clear
ReDim vArr(1 To aRows, aCols)
vArr = R.Value

wsTmp.Range("A1").Resize(UBound(vArr, 1), _
UBound(vArr, 2)).Value = vArr

ReDim vArr(1 To aRows / 2, aCols)

vArr = wsTmp.Range("A1").Offset(aRows / 2, 0). _
Resize(aRows / 2, UBound(vArr, 2)).Value

wsTmp.UsedRange.Clear

wsHalf.Range("A1").Resize(UBound(vArr, 1), _
UBound(vArr, 2)).Value = vArr
End Sub

You will need some working space, might need to add a temp
sheet to some workbook.
Array size is limited to sheet dimensions. So not millions
of rows, I havn't tried with 10,000 x 100 items.
Array must be 2D, even if only one row or col of data.
Lbound must not be 0 (try Option base 1).

Should be able to re-dimension & re-populate your array
with any "rectangular area" within the original array.

Regards
Peter


-----Original Message-----
Suppose I have a 2D variant array, and I want to remove

the first row
from it. Is there a really fast way to do that, rather
manually copying

every value into a new variant array? Perhaps using API

calls or

manipulating the underlying SAFEARRAY structure?

Also, a related question. Suppose I have a Double array

with millions

of items in the array. Is there a quick way to chop it

in half, perhaps

by altering the pointer to the first index and altering

the number of

items in the SAFEARRAY?

I am not experienced with any of this stuff, but I

thought it might be

possible. It would useful to me, if possible. Any help

would be

appreciated.
.

Remove First Row from Variant Array FAST?
 

They are two separate questions. THere may be methods that are general
enough to be applied without respect to dimension, and if that is the
case, it doesn't matter that i posed two questions.

However, with respect to your wanting to know the left half or right
half, does it matter? Even if it mattered because different algorithms
would be applied in the different cases, why not answer both
possibilities, or answer one of the possibilities and tell me to figure
out the other.

The fact that I asked to remove the first row should have given you a
clue as to what I really wanted, though...


Alan Beban wrote:

R Avery wrote:

Sorry. Let's say it is a 1-D array, and I want to quickly change it
to contain less items, ideally without reallocating any memory at all.

Your original post in this thread said a 2-D array; now it's 1-D. I hope
noone wasting time on your original request.

Alan Beban

Remove First Row from Variant Array FAST?
 

Tushar Mehta wrote:

Depends on your definition of 2D variant array. If it consists of
multiple 1D arrays each in one element of a 1D array inside a variant,
yes, you can manipulate a particular dimension fast. If it is the more
traditional array of variants, no.

Why are we trying to give general answers in the abstract?

Removing the 1st row of a 2-D 100,000 element array can take less than
1/2 a second, whether or not multiple 1-D arrays are utilized? Is that
"fast"?

"Cutting a 2-D 3000000 element array in half" can, for a particular
half, take less than a 100th of a second; is that "fast"?

Alan Beban

Remove First Row from Variant Array FAST?
 

This is an interesting idea, although you're right - it
would not work for an array with millions of rows because
of the sheet row limitation. Also, the writing and
reading from ranges might make it slow.

Actually for say 50,000 rows it's quite fast. Most of the
sub I posted is for populating the "original" array with
sample data and viewing the resulting "half". Only a
little is for the actual work.

However there are quicker ways that also cater for more
data, notably as suggested by Alan Beban. I would suggest
searching threads in the last four weeks that include his
name. I think you will find what you are looking for.

Regards,
Peter

Remove First Row from Variant Array FAST?
 

R Avery wrote:

. . .
However, with respect to your wanting to know the left half or right
half, does it matter?

Yes.

Even if it mattered because different algorithms
would be applied in the different cases, why not answer both
possibilities . . .

Because I don't want to waste my time describing the longer one while
you're uninterested in giving some guidance as to what "FAST" means.


The fact that I asked to remove the first row should have given you a
clue as to what I really wanted, though...


No need to play cat and mouse about it; perhaps *clues* will be enough
for other responders; it's your problem, not mine.

Alan Beban

Remove First Row from Variant Array FAST?
 

Ok. Let me clarify FAST. When I say manipulating the SAFEARRAY so that
it thinks there is 1 fewer element in the array and incrementing the
pointer to the first item, if this is possible, I expect it to take
0.0001 or less seconds to do this for an array with 10million or
10billion or more items. Reallocating an entire array for 9,999,999,999
elements and copying them over is not FAST. 0.0001 seconds is FAST.


Is it possible to do delete the first item in a 1-D array (so that the
2nd element now becomes the 0th index) or to delete the first row in a
2-D array (so that the 2nd row now becomes the 0th index in the first
dimension) FAST?

Remove First Row from Variant Array FAST?
 

I'm sorry but I don't understand your point. The OP is going on about
the SAFEARRAY construct, which is how VB implements arrays. I offered
what I believe to be a more practical approach using variants. Like
you I don't see the point of providing specifics until the OP
demonstrates a willingness to share specific information her/himself.

And, the performance of an array compared with an array of arrays with
a variant root will be significantly different. The former is
*probably* faster in 'mundane' use; the latter allows one to (a) create
structures that are impossible with the former, and (b) manipulate them
in ways that are either impossible with or much more efficient than the
former.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar Mehta wrote:

Depends on your definition of 2D variant array. If it consists of
multiple 1D arrays each in one element of a 1D array inside a variant,
yes, you can manipulate a particular dimension fast. If it is the more
traditional array of variants, no.

Why are we trying to give general answers in the abstract?

Removing the 1st row of a 2-D 100,000 element array can take less than
1/2 a second, whether or not multiple 1-D arrays are utilized? Is that
"fast"?

"Cutting a 2-D 3000000 element array in half" can, for a particular
half, take less than a 100th of a second; is that "fast"?

Alan Beban

Remove First Row from Variant Array FAST?
 

"JE McGimpsey" wrote...
....
If not done strictly for training, eventually the Ensign learns to say
"get your own d**n rock" on about the 2nd iteration. However, I also
learned that when the Captain plays Bring Me a Rock, it's *always* for
training...<g

And those ensigns who tell a seaman to bring them a rock are destined for
flag rank?

Remove First Row from Variant Array FAST?
 

"R Avery" wrote...
Ok. Let me clarify FAST. When I say manipulating the SAFEARRAY so that
it thinks there is 1 fewer element in the array and incrementing the
pointer to the first item, if this is possible, I expect it to take
0.0001 or less seconds to do this for an array with 10million or
10billion or more items. Reallocating an entire array for 9,999,999,999
elements and copying them over is not FAST. 0.0001 seconds is FAST.

Unlikely you could manipulate the SAFEARRAY construct as you'd like. The key
part of the SAFEARRAY structure is the pointer to the data, meaning that the
data is NOT stored in the SAFEARRAY structure itself. Depending on exactly
how VBA manages pointers and memory allocation, you could thoroughly fubar
your system with memory leaks by manipulating the pointer directly.

I suppose it'd be possible for you to create a new SAFEARRAY structure,
modify the dimension bounds, and assign the pointer to the desired location
in the original SAFEARRAY's allocated memory. JUST DON'T FREE THAT ARRAY!

Is it possible to do delete the first item in a 1-D array (so that the
2nd element now becomes the 0th index) or to delete the first row in a
2-D array (so that the 2nd row now becomes the 0th index in the first
dimension) FAST?

Who knows? But without a doubt the fastest way to *use* subarrays of an
existing array is just to use the existing array AS-IS with suitable new
starting and ending indices. For example, addressing the middle third of the
array a(1 To 150000) would ideally be done as

For i = 50001 To 100000
x = f(a(i))
Next i

There are obvious ways to generalize this and make it more flexible.

Remove First Row from Variant Array FAST?
 

In article ,
"Harlan Grove" wrote:

And those ensigns who tell a seaman to bring them a rock are destined for
flag rank?

Yup - but that's why the Navy has Chiefs...