F9

[g48dd]

Excel 2003: I need to recalculate after I run a macro, is there a line I could put at the end of my macro that would do this for me, instead of having to use F9?

Thank you
Ken

[jonix]

Add this code at the end of your macro:

With ActiveSheet
.EnableCalculation = False
.EnableCalculation = True
.Calculate
End With

[g48dd]

Hello Jonix, I can usually adjust things so they work but I am having problems with this, I am getting can not execute in break mode. This is what the end of my code looks like:

Set rngCells = Union(rngCells, Range("A2677,A2696,A2699,A2731,A2765,A2768,A2800,A2803,A2835,A2838,A2869"))
Set rngCells = Union(rngCells, Range("A2884,A2887,A2919,A2922,A2941,A2944,A2955,A2958,A2977,A2980"))

With rngCells.Interior
.ColorIndex = 37
.Pattern = xlSolid

With ActiveSheet
.EnableCalculation = False
.EnableCalculation = True
.Calculate
End With

End Sub

[Ken Puls]

Does that code compile? (Debug-->Compile VBA Project)

It looks to me like you're missing an "End With" right after the .Pattern=xlSolid

Jonix, curious why you turn calculation off, then on, then calculate? I would have thought Activesheet.Calculate would be sufficient? Have you found that not to be the case?

[g48dd]

Absolutely, that was it, runs fine.

Thank you
Everyone
Ken

[jonix]

Jonix, curious why you turn calculation off, then on, then calculate? I would have thought Activesheet.Calculate would be sufficient? Have you found that not to be the case?

Hey! I found this in official MSDN :) Tested in my Excel and it worked - for entire workbook. Thats interesting because "ActiveSheet" normaly is not all sheets in workbook.. but it works (recalculates) for all sheets.

I decided to keep the original Microsoft advice - just in case. Maybe on some Excel versions it is required?

[Ken Puls]

Weird...

I can see why it would work that way. I've always forced my workbooks/worksheets to calculate using the following (depending on what I needed.)


Application.Calculate
ActiveSheet.Calculate
Range.Calculate