How do I expedite this Excel VBA macro?

I know a little bit of VBA but I am always looking for smarter ways (the ways you people work!!).

Here is what I am trying to do. I have to deal with some data that has

Year | Month | Day | Hour | Minute | Data1 | Data2.... | DataN

each in a separate column and has thousands of rows. The number of "Data" columns is defined by the user during the run time (at least 1 and max 100). The data may have any time step eg every minute, 5 mins, 10 mins, each hour, daily and so on. The user then specifies the output data interval which will be always greater than input data interval.

So, this macro is supposed to write the data in the interval specified by the user. The data in each column in between the output time step has to be added togther. See following two tables:

Input:

Yr     Mth  Day Hr  Min Data_1  Data_2

2010    2   7   8   0   1.01    2.01

2010    2   7   8   5   1.02    2.02

2010    2   7   8   10  1.03    2.03

2010    2   7   8   15  1.04    2.04

2010    2   7   8   20  1.05    2.05

2010    2   7   8   25  1.06    2.06

2010    2   7   8   30  1.07    2.07

2010    2   7   8   35  1.08    2.08

2010    2   7   8   40  1.09    2.09

2010    2   7   8   45  1.10    2.10

2010    2   7   8   50  1.11    2.11

2010    2   7   8   55  1.12    2.12

2010    2   7   9   0   1.13    2.13

2010    2   7   9   5   1.14    2.14

Output:

Yr     Mth  Day Hr  Min Data_1  Data_2

201开发者_运维问答0    2   7   8   0   1.01    2.01

2010    2   7   8   15  3.09    6.09

2010    2   7   8   30  3.18    6.18

2010    2   7   8   45  3.27    6.27

2010    2   7   9   0   3.36    6.36

So, the input data is every 5 minute and the output data has to be every 15 minute. Each row of data between successive 15 minutes interval have to be added together.

I have this thing working as I use two loops traversing horizontally and vertically and writing the values to the spreadsheet at each step. But it is really slow.

Any help to expedite this process will be greatly appreciated.

Thanks in advance

-MP

** Code I have been using to write the processed data to a text file:

Open FName For Output Access Write As #FNum
For RowNdx = StartRow To endrow
done = Int((RowNdx / endrow) * 100)
Application.StatusBar = "Exporting *.gag file... (" & done & " % done )"
    WholeLine = ""
    For colNdx = StartCol To Endcol
        If Sheets("Output").Cells(RowNdx, colNdx).Text = "" Then
            CellValue = ""
        Else
           CellValue = Sheets("Output").Cells(RowNdx, colNdx).Text
        End If
        WholeLine = WholeLine & CellValue & sep
    Next colNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(sep))
    Print #FNum, WholeLine
Next RowNdx
Close #FNum

Is there a way to write a range to a text file at once. I am trying to avoid looping.

Thanks for your help

MPD

Have you tried setting

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

before your loops?

Remember to set

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

after your loops have completed.

You might also save all of your data to an array in your loops and then writing the entire array as a range to your output spreadsheet, instead of writing the values cell-by-cell, which is what it seems like you are doing now.