convert excel data to 2-D array using macro
 

pls how do i convert excel sheet data to 2-D array using VBA macro. i intend
filtering and extracting info from this array. thanks.

convert excel data to 2-D array using macro
 

Hi,

Dim v as Variant
v=raNGE("A1:C100")
--- v is now a 2-d array

Using an array might not be the bets way though. It might be more efficient
do either:
- filter the range through code the build the array based on the shorter
filtered cells
- filter the range through code and work with this shorter range of cells
directly without using an array.
This will depend on what you are tryiong to accomplish in later processing
of the array.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"David" wrote:

pls how do i convert excel sheet data to 2-D array using VBA macro. i intend
filtering and extracting info from this array. thanks.

convert excel data to 2-D array using macro
 

thanks sebastien.
but i want to represent the whole data sheet with an array. the array should
know the length of data as the data migth be dynamic. more over i deal with
data as long as 10,000 rows. pls kindly assist me. thanks

"sebastienm" wrote:

Hi,

Dim v as Variant
v=raNGE("A1:C100")
--- v is now a 2-d array

Using an array might not be the bets way though. It might be more efficient
do either:
- filter the range through code the build the array based on the shorter
filtered cells
- filter the range through code and work with this shorter range of cells
directly without using an array.
This will depend on what you are tryiong to accomplish in later processing
of the array.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"David" wrote:

pls how do i convert excel sheet data to 2-D array using VBA macro. i intend
filtering and extracting info from this array. thanks.

convert excel data to 2-D array using macro
 

David,
With:
Dim v as Variant
v=Activesheet.Usedrange
--- v is now a 2-d array of values corresponding to the whole UsedRange
range of the active sheet.
However i would think you would encounter memory issues with large
arrays, probably depends on machines available memory. I remember having
issues one day with a 4,000-row array using a similar method.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"David" wrote:

thanks sebastien.
but i want to represent the whole data sheet with an array. the array should
know the length of data as the data migth be dynamic. more over i deal with
data as long as 10,000 rows. pls kindly assist me. thanks

"sebastienm" wrote:

Hi,

Dim v as Variant
v=raNGE("A1:C100")
--- v is now a 2-d array

Using an array might not be the bets way though. It might be more efficient
do either:
- filter the range through code the build the array based on the shorter
filtered cells
- filter the range through code and work with this shorter range of cells
directly without using an array.
This will depend on what you are tryiong to accomplish in later processing
of the array.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"David" wrote:

pls how do i convert excel sheet data to 2-D array using VBA macro. i intend
filtering and extracting info from this array. thanks.

convert excel data to 2-D array using macro
 

hello sebastienm,
thanks for your kind response. i'll try your method. i used some form of
criteria to get size of data, i.e row length and column length. with these i
created arrays which i used to solve the problem. when ever data size is
increaed the program detects it and changes arrays automatically. thanks a
lot!

"sebastienm" wrote:

David,
With:
Dim v as Variant
v=Activesheet.Usedrange
--- v is now a 2-d array of values corresponding to the whole UsedRange
range of the active sheet.
However i would think you would encounter memory issues with large
arrays, probably depends on machines available memory. I remember having
issues one day with a 4,000-row array using a similar method.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"David" wrote:

thanks sebastien.
but i want to represent the whole data sheet with an array. the array should
know the length of data as the data migth be dynamic. more over i deal with
data as long as 10,000 rows. pls kindly assist me. thanks

"sebastienm" wrote:

Hi,

Dim v as Variant
v=raNGE("A1:C100")
--- v is now a 2-d array

Using an array might not be the bets way though. It might be more efficient
do either:
- filter the range through code the build the array based on the shorter
filtered cells
- filter the range through code and work with this shorter range of cells
directly without using an array.
This will depend on what you are tryiong to accomplish in later processing
of the array.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"David" wrote:

pls how do i convert excel sheet data to 2-D array using VBA macro. i intend
filtering and extracting info from this array. thanks.