As you suggested in another post, yes, if you transpose the array you should
have an array with rows and columns switched allowing you to redim the rows.
Another potential solution is to dimension your output array without the
preserve option, and just use the maximum number of rows (probably the
number of rows in your input array). This will use some extra memory, but
may well be a great solution. each element in the array will use something
like 16 bytes of memory I think, for an array of variants. You can try it
works timewise. And yes, somewhere you asked if numerous REDIMs would take
extra time. Yes, not sure how much.. Best to oversize the array in blocks
and if needed shrink it back to the final used size.
If you simply REDIM the array to the size of your input array, and assign it
to a range (smaller than your output array) when ready, it should work. the
extra elements of the array should be ignored.
' if you want to try just OVERSIZING the array, perhaps something like this.
' this particular example assumes a fixed known number of columns
const cols as long = 4
dim inputarray as variant, outputarray as variant()
dim inputrange as range, outputrange as range
dim rowin as long
dim rowsin as long, rowsout as long
dim colout as long
set inputrange = .
inputarray = inputrange.value2
rowsin = ubound(inputarray,1)
redim outputrange(1 to rowsin, 1 to cols)
for rowin = 1 to rowsin
if inputarray(rowin, 1) >= 1000 then 'criteria to send to output range
rowsout=rowsout + 1
for colout = 1 to cols
outputarray(rowsout, colout) = inputarray(rowin, colout)
next colout
end if
next rowin
set outputrange = .
outputrange.value2 = outputarray
' If you wanted to try TRANSPOSE, and save memory, perhaps something like
this.
' this particular example assumes a fixed known number of columns
const cols as long = 4
dim inputarray as variant, outputarray() as variant
dim inputrange as range, outputrange as range
dim rowin as long
dim rowsin as long, rowsout as long
dim colout as long
set inputrange = .
inputarray = inputrange.value2
rowsin = ubound(inputarray,1)
rowsout=0
for rowin = 1 to rowsin
if inputarray(rowin, 1) >= 1000 then 'criteria to send to output range
rowsout=rowsout + 1
redim preserve outputrange(1 to cols, 1 to rowsout)
for colout = 1 to cols
outputarray(colout, rowsout) = inputarray(rowin, colout)
next colout
end if
next rowin
set outputrange = .
outputrange.value2 = worksheetfunction.transpose(outputarray)
If the input and output ranges would be the same size, you could just use
one array for input and output, and obviously no need for transposing or any
funny business.
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of tangledweb
Sent: Wednesday, April 11, 2012 12:09 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ redim preserve seems to only allow columns
increase which is backwards
I want to use an array to store values as more efficient that doing it cell
by cell,
then copy the entire array into the sheet range when done. But the size of
the
array not know initially. I do know that number of columns, but not the
number
of rows. So I will have to do some redim preserve operations along the
way.
But redim preserve says you can only increase the outer value which is the
columns.
That makes no sense to me as you almost always need to increase the number
of
rows in a spreadsheet as new data is added. So I think I must be missing
something.
I can define the array inverted switching the rows and columns although
clumsy to
work with but then copying it to the spreadsheet range as one assignment
sheetrange = myarray will not work correctly.
What am I missing here?
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
