Excel VBA - Fast Loop

Hi Everyone
I have a huge database with Products, Quantities and Dates. The macro goes through this database (sheet "Data") and distribute them into the "Summary" sheet.
The loop is already created and it's running fine but it's taking too long (more than a hour) to cover the whole file. There are several loops in the same macro checking different data. What I'm trying to find another method to speed it up. Attached is a sample file with the loop and a sample data.
Any help will be very appreciated.

I will describe below a sample of a loop I'm using today.

Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Summary")
iDate = Date
fDate = iDate + 1

lRow = 2
Do While ws1.Cells(lRow, "A") <> ""
    nRow = 11
    Do While ws2.Cells(nRow, "B") <> ""
        If ws2.Cells(nRow, "B") = ws1.Cells(lRow, "A") Then
            If ws1.Cells(lRow, "B") < iDate Then
                ws2.Cells(nRow, "C") = ws2.Cells(nRow, "C") + ws1.Cells(lRow, "D")
            Else
                For iCol = 4 To 10
                    If ws1.Cells(lRow, "B") >= iDate And ws1.Cells(lRow, "B") < fDate Then
                        ws2.Cells(nRow, iCol) = ws2.Cells(nRow, iCol) + ws1.Cells(lRow, "D")
                    End If
                    iDate = iDate + 1
                    fDate = fDate + 1
                Next iCol
            End If
        End If
    Loop
Loop

Hi

Please remember our rule about code tags see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I'll add them for you this time.

Secondly

Whenever you find yourself creating looping code stop and think is this really necessary? Most of the time it isn't. Loops are inherently slow when they have to jump back and forth between VBA and the Excel application - there's a big time overhead at each pass through the loop.

In your case you should read the data into a VBA array and process the array with the loops. That way you only read and write to Excel once rather than hundreds if not thousands,

However why are you using VBA at all? Adding an extra column to your Data and using a Pivot Table will produce the results immediately. If you really want the dates in columns then just drag the date2 field to the columns area of the PT field list.

See attached

Excel's Power Pivot Add in can of course handle many separate tables in one PT

If you're going to stick with VBA then the only way to speed up your looping macro is as I said to write all the data into a VBA array, then process the array with your loops, finally writing the result back to the Excel sheet.

That's one read/write process, not the thousands you are currently performing.

Maybe you are missing the point.

I'm saying that rather than processing/reading the data FROM the Excel sheet on every iteration of the loop(s) first read the whole data into a VBA array.
Then change the loops to process the data in the VBA array NOT the Excel sheet.

The point is that with all the data safely inside VBA you can process it at lightening speed since you're not jumping back and forth to Excels. There is a significant time overhead each time you do that.

Where you are now taking over an hour, by using an array it will probably take a minute or so.