Refresh display of Range when ScreenUpdating is False

Question

Hi

Is there a way to refresh the display of only a specified range of cells from VBA, when ScreenUpdating = False?

What I mean is the following:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("A1").Calculate
Range("A1").SomeFunctionThatRefreshesThis

...

Karl

Answer 1

If ScreenUpdating is set to false then the update will not be shown until ScreenUpdating is set to True. The only way that you can make this act the way you want is to use the ScreenUpdating property in a more fine grained way. For example:

Application.Calculation = xlCalculationManual

Range("A1").Calculate
Application.ScreenUpdating = False
Range("A1").SomeFunctionThatRefreshesThis
Application.ScreenUpdating = True
Application.ScreenUpdating = False
...

Answer 2

You can calculate a specific range of cells by using the RangeCalc add-in from Charles Williams (Descision Models)

http://www.decisionmodels.com/downloads.htm#addload

The application-level screenupdating setting will of course prevent you from seeing the changes though, but without it set to false the overall effect should be similar.