Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array speed
I use the following code to write the data from a (8615 * 24) array to
worksheet It is very fast before. But I don't know why it is slow now. Today, I spend 2.5 hours for this. I want to know why and how to speed up. Many thanks. Dim i As Long, j As Byte For i = LBound(RMaster) To UBound(RMaster) For j = 0 To 23 Cells(i + 2, j + 1 = RMaster(i, j) Next Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array speed
I put the code below in a new workbook and it took under 10 seconds. Try
doing hte same. I think it is the workbook and not the macro. You may have a lot of data or fromating in the workbook which would slow down the macro. But it also may be your PC. I would try putting the workbook on another PC to see what happens. Sometimes deleting rows that are not used in teh worksheet may help. If you worksheet goes to column Z then highlight all the columns to the right of you data and delete the columns not used. Repeat for the rows. Try for all worksheets. Sub test() Dim RMaster(8615, 24) Dim i As Long, j As Byte For i = LBound(RMaster) To UBound(RMaster) For j = 0 To 23 Cells(i + 2, j + 1) = RMaster(i, j) Next Next End Sub "leungkong" wrote: I use the following code to write the data from a (8615 * 24) array to worksheet It is very fast before. But I don't know why it is slow now. Today, I spend 2.5 hours for this. I want to know why and how to speed up. Many thanks. Dim i As Long, j As Byte For i = LBound(RMaster) To UBound(RMaster) For j = 0 To 23 Cells(i + 2, j + 1 = RMaster(i, j) Next Next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array speed
I want to know why and how to speed up.
Dim i As Long, j As Byte Hi. Just another approach... Sub Demo() Dim RMaster(8615, 24) Dim d '// Load RMaster w/ data...etc d = Dimensions(RMaster) [B1].Resize(d(0), d(1)) = RMaster End Sub Private Function Dimensions(m) As Variant Dimensions = Array( _ UBound(m, 1) - LBound(m, 1) + 1, _ UBound(m, 2) - LBound(m, 2) + 1) End Function Dim i As Long, j As Byte I believe the consensus here in the newsgroup is that "Byte" is not often used for a looping type. There just doesn't seem to be any speed improvement. Note that the number of columns is 25 (ie 0 to 24) Perhaps you could use something like this: Dim RMaster(1 To 8615, 1 To 24) = = = = = = = HTH :) Dana DeLouis leungkong wrote: I use the following code to write the data from a (8615 * 24) array to worksheet It is very fast before. But I don't know why it is slow now. Today, I spend 2.5 hours for this. I want to know why and how to speed up. Many thanks. Dim i As Long, j As Byte For i = LBound(RMaster) To UBound(RMaster) For j = 0 To 23 Cells(i + 2, j + 1 = RMaster(i, j) Next Next |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speed of array formula | Excel Worksheet Functions | |||
Speed of fixed array versus dynamic array | Excel Programming | |||
Using an Array instead of a Vlookup to improve speed | Excel Programming | |||
Speed up performance for very big array | Excel Programming | |||
VBA - speed up macro, working with array? | Excel Programming |