T
TKT-Tang
Re : Excel VBA Read and Write between the WorkSheet and an Array()
1. Enter an Excel spreadsheet and Insert > Define > Name, RangeA,
pertaining to A1:A100.
2. RangeA is a filled range but interspersed with blank cells in order
to segregate the contents thereof.
3. By using VBA, RangeA is copied to RangeB (same as Insert > Define >
Name, in the range of B1:B100) and sorted thereof.
4. RangeB has become a contiguous range (albeit several blank cells
trail in the lower region of the range).
5. And then, RangeB is read into an Array().
6. Last but not least, Array() is fed back to RangeB in a pattern
that's commensurate with that of RangeA.
7.Meaning that, where RangeA is filled, the corresponding cells in
RangeB will be filled with the contents of Array() ; where RangeA is
interspersed with a blank cell, so there will one alike in RangeB.
8. However, the process of Step 7 consumes considerable time. Is it due
to the requirement of writing to worksheet being much time-consuming ?
9. You see, Steps 3 and 5 are fairly fast. And there, the reads and
writes are presumably more intensive than the corresponding process of
Step 7.
10. Practical experience (in this particular case, for the purpose of
delineation) shows that Steps 3 and 5 would take less than a second
(combined) while Step 7 alone could be 30 seconds or more.
11. What could be done now to speed up the process of Step 7 ?
12. Please share your experience. Regards.
1. Enter an Excel spreadsheet and Insert > Define > Name, RangeA,
pertaining to A1:A100.
2. RangeA is a filled range but interspersed with blank cells in order
to segregate the contents thereof.
3. By using VBA, RangeA is copied to RangeB (same as Insert > Define >
Name, in the range of B1:B100) and sorted thereof.
4. RangeB has become a contiguous range (albeit several blank cells
trail in the lower region of the range).
5. And then, RangeB is read into an Array().
6. Last but not least, Array() is fed back to RangeB in a pattern
that's commensurate with that of RangeA.
7.Meaning that, where RangeA is filled, the corresponding cells in
RangeB will be filled with the contents of Array() ; where RangeA is
interspersed with a blank cell, so there will one alike in RangeB.
8. However, the process of Step 7 consumes considerable time. Is it due
to the requirement of writing to worksheet being much time-consuming ?
9. You see, Steps 3 and 5 are fairly fast. And there, the reads and
writes are presumably more intensive than the corresponding process of
Step 7.
10. Practical experience (in this particular case, for the purpose of
delineation) shows that Steps 3 and 5 would take less than a second
(combined) while Step 7 alone could be 30 seconds or more.
11. What could be done now to speed up the process of Step 7 ?
12. Please share your experience. Regards.