Member Avatar for shahji

Dear all,

I have the following macro and have tried a few things to make it run quicker but it still takes too long.
the below code only runs on one column but i have 40 such columns. so all in all macro takes about 30 mins to run.
Any idea how i can make it faster or other ways of achieving the same result.

Sub Sumproduct()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual

Dim LastRow As Long


With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B3").Resize(LastRow - 1).Formula = "=SUMPRODUCT((S6OBData!A:A=A3)*(S6OBData!B:B=B$2),S6OBData!C:C)"

Range("B3:B2000").Value = Range("B3:B2000").Value

End With

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

End Sub

many thanks for help,

Shah

  • 2 Contributors
  • 2 Replies
  • 165 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by shahji
Member Avatar for Salem

> Range("B3:B2000").Value = Range("B3:B2000").Value
What does this do?
Not a lot as far as I can see.

Member Avatar for shahji

Thanks Salem,

Range("B3:B2000").Value = Range("B3:B2000").Value

The Code puts the formula in each cell and formula gets the value from other sheet, at the end it changes all the cells to values rather than having the formula behind the scenes.

This is just one column and it takes about 40 seconds to complete this and I will be running this on 80 columns on two sheets. It is taking about 30mins on one sheet. Was wondering if there is another way to get the data.

Edited by shahji because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.