I have a procedure that loads a 2D dynamic excel range inot a VBA
array.

Range("xlEntireXlArray").Activate
With Range("xlEntireXlArray")
ExcelRowCount = .Rows.Count
ExcelColumnCount = .Columns.Count
UploadArray = .Resize(ExcelRowCount, ExcelColumnCount)
End With

The VBA array will always represent the dynamic excel range.

What if I want more control over the columns with the excel range that
actually get loaded into the array. Assume the excel range is 10 rows
by 15 columns. But I do not want all 15 columns in the VBA array. If
I only wanted columns 1-5, and 7 (6 columns) loaded into the array, is
it possible to load these columns individually? How would you write
the 6 different load statements? I am not looking for a form here
that gives me the option of picking columns. I will want to do this
manually with code.

Secondly when I now pull these 6 columns into my VBA array, I want the
second dimension of my VBA array to be larger than the number of
columns I pulled in. Say I want it to be 20 elements across instead
of just 6. Do I just simply dimension it for 20? I want it to be
large because I want to do some additional calcs and store the values
in the VBA array.

TS

Terry,

For the second part of your question,

Redim Preserve UploadArray(Lbound(UploadArray,1) to Ubound(UploadArray,1),
Lbound(UploadArray,2) to Ubound(UploadArray,2) + 100)

Will let you add 100 columns (for example) and maintain the data you have
already put into the array. Note that Redim Preserve only lets you increase
the last dimension of the array.

For the first question....

It's more like personal philosophy, but my technique is to read everything
as fast as possible (sheet reads take time, sheet writes take LOTS of time).

After that, it is always possible to transfer array components to another
array, or a collection, etc. It also, for you, really depends on what you
might be writing back to the sheet, and how that needs to be arranged.

Alex J


wrote in message
om...
I have a procedure that loads a 2D dynamic excel range inot a VBA
array.

Range("xlEntireXlArray").Activate
With Range("xlEntireXlArray")
ExcelRowCount = .Rows.Count
ExcelColumnCount = .Columns.Count
UploadArray = .Resize(ExcelRowCount, ExcelColumnCount)
End With

The VBA array will always represent the dynamic excel range.

What if I want more control over the columns with the excel range that
actually get loaded into the array. Assume the excel range is 10 rows
by 15 columns. But I do not want all 15 columns in the VBA array. If
I only wanted columns 1-5, and 7 (6 columns) loaded into the array, is
it possible to load these columns individually? How would you write
the 6 different load statements? I am not looking for a form here
that gives me the option of picking columns. I will want to do this
manually with code.

Secondly when I now pull these 6 columns into my VBA array, I want the
second dimension of my VBA array to be larger than the number of
columns I pulled in. Say I want it to be 20 elements across instead
of just 6. Do I just simply dimension it for 20? I want it to be
large because I want to do some additional calcs and store the values
in the VBA array.

TS

Range("xlEntireXlArray").Activate
With Range("xlEntireXlArray")
ExcelRowCount = .Rows.Count
ExcelColumnCount = .Columns.Count
UploadArray = .Resize(ExcelRowCount, ExcelColumnCount)
End With

could be replaced with

dim UpLoadArray as Variant
UpLoadArray = Range("xlEntireXlArray").Value

You must assign a contiguous rectangular area - you can't assign
discontiguous ranges to an array (I believe you can but it will only use the
first area in the range).

--
Regards,
Tom Ogilvy

wrote in message
om...
I have a procedure that loads a 2D dynamic excel range inot a VBA
array.

Range("xlEntireXlArray").Activate
With Range("xlEntireXlArray")
ExcelRowCount = .Rows.Count
ExcelColumnCount = .Columns.Count
UploadArray = .Resize(ExcelRowCount, ExcelColumnCount)
End With

The VBA array will always represent the dynamic excel range.

What if I want more control over the columns with the excel range that
actually get loaded into the array. Assume the excel range is 10 rows
by 15 columns. But I do not want all 15 columns in the VBA array. If
I only wanted columns 1-5, and 7 (6 columns) loaded into the array, is
it possible to load these columns individually? How would you write
the 6 different load statements? I am not looking for a form here
that gives me the option of picking columns. I will want to do this
manually with code.

Secondly when I now pull these 6 columns into my VBA array, I want the
second dimension of my VBA array to be larger than the number of
columns I pulled in. Say I want it to be 20 elements across instead
of just 6. Do I just simply dimension it for 20? I want it to be
large because I want to do some additional calcs and store the values
in the VBA array.

TS

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following will do what you suggest. Starting with a range of 10 rows
and 15 columns, it loads the range values into arr (4th line), replaces
the 6th "column" of arr with the 7th (5th line), reloads arr with the
resulting 1st 6 "columns", (6th line), then increases the number of
"columns" of arr to 20 (7th line).

Sub testIt()
Dim rng As Range, arr As Variant, numRows As Long
Set rng = Sheets(2).Range("A1:O10")
numRows = rng.Rows.Count
arr = rng.Value
ReplaceSubArray arr, SubArray(arr, 7, 7, 1, numRows), 1, 6
arr = SubArray(arr, 1, 6, 1, numRows)
ReDim Preserve arr(numRows, 20)
End Sub

Alan Beban

wrote:
I have a procedure that loads a 2D dynamic excel range inot a VBA
array.

Range("xlEntireXlArray").Activate
With Range("xlEntireXlArray")
ExcelRowCount = .Rows.Count
ExcelColumnCount = .Columns.Count
UploadArray = .Resize(ExcelRowCount, ExcelColumnCount)
End With

The VBA array will always represent the dynamic excel range.

What if I want more control over the columns with the excel range that
actually get loaded into the array. Assume the excel range is 10 rows
by 15 columns. But I do not want all 15 columns in the VBA array. If
I only wanted columns 1-5, and 7 (6 columns) loaded into the array, is
it possible to load these columns individually? How would you write
the 6 different load statements? I am not looking for a form here
that gives me the option of picking columns. I will want to do this
manually with code.

Secondly when I now pull these 6 columns into my VBA array, I want the
second dimension of my VBA array to be larger than the number of
columns I pulled in. Say I want it to be 20 elements across instead
of just 6. Do I just simply dimension it for 20? I want it to be
large because I want to do some additional calcs and store the values
in the VBA array.

TS

Can someone explain to me what the numbers represent, I try to count
it from the data, doesn't look like (rows, colums)

Workbooks.OpenText Filename:="C:\ACTEXTR0.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0,
_
9), Array(16, 1), Array(22, 1), Array(24, 1), Array(26, 1),
Array(29, 9), Array _
(31, 1), Array(73, 9), Array(77, 5), Array(85, 1), Array(92,
1), Array(103, 1), _
Array(115, 9), Array(121, 1), Array(146, 1), Array(171, 1),
Array(381, 1))
ChDir "C:\"



wrote in message . com...
I have a procedure that loads a 2D dynamic excel range inot a VBA
array.

Range("xlEntireXlArray").Activate
With Range("xlEntireXlArray")
ExcelRowCount = .Rows.Count
ExcelColumnCount = .Columns.Count
UploadArray = .Resize(ExcelRowCount, ExcelColumnCount)
End With

The VBA array will always represent the dynamic excel range.

What if I want more control over the columns with the excel range that
actually get loaded into the array. Assume the excel range is 10 rows
by 15 columns. But I do not want all 15 columns in the VBA array. If
I only wanted columns 1-5, and 7 (6 columns) loaded into the array, is
it possible to load these columns individually? How would you write
the 6 different load statements? I am not looking for a form here
that gives me the option of picking columns. I will want to do this
manually with code.

Secondly when I now pull these 6 columns into my VBA array, I want the
second dimension of my VBA array to be larger than the number of
columns I pulled in. Say I want it to be 20 elements across instead
of just 6. Do I just simply dimension it for 20? I want it to be
large because I want to do some additional calcs and store the values
in the VBA array.

TS

Each element of the fieldinfo array consists of two dimensional array.

Array(77,5) for example.

You can look in VBA's help for texttocolumns and you'll see something like this
for the second number:

XlColumnDataType can be one of these XlColumnDataType constants.
xlGeneralFormat. General
xlTextFormat. Text
xlMDYFormat. MDY Date
xlDMYFormat. DMY Date
xlYMDFormat. YMD Date
xlMYDFormat. MYD Date
xlDYMFormat. DYM Date
xlYDMFormat. YDM Date
xlEMDFormat. EMD Date
xlSkipColumn. Skip Column

(I think in earlier versions of the help, they actually gave the numbers instead
of xl's constants):

1 xlGeneralFormat. General
2 xlTextFormat. Text
3 xlMDYFormat. MDY Date
4 xlDMYFormat. DMY Date
5 xlYMDFormat. YMD Date
6 xlMYDFormat. MYD Date
7 xlDYMFormat. DYM Date
8 xlYDMFormat. YDM Date
10 xlEMDFormat. EMD Date
9 xlSkipColumn. Skip Column

Copied from the help:
You can use xlEMDFormat only if Taiwanese language support is installed and
selected. The xlEMDFormat constant specifies that Taiwanese era dates are being
used.

And the first column is the position in the text (but start counting at 0).

So Array(Array(0, 1), Array(4, 2))
would start at the first position and treat it as General.
(actually the first 4 characters would go into that cell. The field goes until
the next field starts or until the end of line.)

Then position 5 (we started counting at 0) to the next field would be treated as
text.



sals wrote:

Can someone explain to me what the numbers represent, I try to count
it from the data, doesn't look like (rows, colums)

Workbooks.OpenText Filename:="C:\ACTEXTR0.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0,
_
9), Array(16, 1), Array(22, 1), Array(24, 1), Array(26, 1),
Array(29, 9), Array _
(31, 1), Array(73, 9), Array(77, 5), Array(85, 1), Array(92,
1), Array(103, 1), _
Array(115, 9), Array(121, 1), Array(146, 1), Array(171, 1),
Array(381, 1))
ChDir "C:\"

wrote in message . com...
I have a procedure that loads a 2D dynamic excel range inot a VBA
array.

Range("xlEntireXlArray").Activate
With Range("xlEntireXlArray")
ExcelRowCount = .Rows.Count
ExcelColumnCount = .Columns.Count
UploadArray = .Resize(ExcelRowCount, ExcelColumnCount)
End With

The VBA array will always represent the dynamic excel range.

What if I want more control over the columns with the excel range that
actually get loaded into the array. Assume the excel range is 10 rows
by 15 columns. But I do not want all 15 columns in the VBA array. If
I only wanted columns 1-5, and 7 (6 columns) loaded into the array, is
it possible to load these columns individually? How would you write
the 6 different load statements? I am not looking for a form here
that gives me the option of picking columns. I will want to do this
manually with code.

Secondly when I now pull these 6 columns into my VBA array, I want the
second dimension of my VBA array to be larger than the number of
columns I pulled in. Say I want it to be 20 elements across instead
of just 6. Do I just simply dimension it for 20? I want it to be
large because I want to do some additional calcs and store the values
in the VBA array.

TS

--

Dave Peterson