CODEWITHSUNDEEP
vbaexcel
Coopa
Coopa

Reputation: 213

VBA to calculate a specific selection in a table (excel)

I am trying to create a VBA that will set the calculation of a specific column/range of columns to manual, within a table that is set to calculate automatically.

Because the dashboard is already so slow I do not want these specific columns/range to calculate automatically like the rest of the table. I'm using this vba but my whole table is still calculating automatically when I first load my cust # into my excel dashboard. 

Sub Recalc()
Selection.Calculate
End Sub

Any ideas?

Upvotes: 2

Views: 2112

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9907

You cannot directly turn calculations on or off to a specific section, it has to apply to the application. Your best bet would be to turn all calculations off for the application, but then set a change macro to recalculate when changeshappen with a specific range. This would need to be written in the appropriate SHEET section of a your VBA editor, not MODULE

Example:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B3:Z100")) Is Nothing Then
Range("B3:Z100").Calculate
End If

End Sub

Upvotes: 2

Related Questions