Hello, Excellers and welcome back to another #MacroMonday blog post in my#Excel 2019 series. Today let’s look at how to update or calculate only selected or specific cells in your Excel worksheet. This is a very useful macro to have in your Excel toolbox, especially if you have a very large Excel solution and you just want to update only a few cells rather than update the whole Excel worksheet.

Now, there is a way to do this exact same action without writing a macro. I previously covered this in a blog post and also a YouTube video. The links are below.

Formula Friday – Calculate Only Some Of Your Workbook’s Formulas

But, I like to do things really quickly once, and just run them again and again. That is why I love VBA and macros. So, for the macro solution, let’s get writing some code right?.

Preparing To Write The Macro.

First, you will need to open the Visual Basic Editor.  There are two ways to do this.  Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic.  Both methods have the same result.   You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook.  What’s the difference?.  If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks.  If you store it in the current workbook then use is restricted to that workbook.

This macro will be useful in the future so I am going to save it in my Personal Macro Workbook.

More About Your Personal Macro Workbook (PMW).

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Starting The Macro.

We need to start off the macro by inserting a New Module.  Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro.  In this example, I have called it simply CalcSelectedCells.  Notice that Excel will automatically enter the end text End Sub to end the Sub Routine.  We simply need to enter the rest of the code between these two lines.

[stextbox id=’info’]
Sub CalcSelectedCells
End Sub
[/stextbox]

Declaring Variables.

Next, we will need to declare either one or more variable. This just ensures that Excel allocated some memory to store that information. We need to just declare one variable in this macro. In this macro, we are creating a memory container for the range that will be selected by the user.

[stextbox id=’info’]
Dim Rnge as Range
[/stextbox]

Setting The Range.

We then set the range as the selection that is made by the Excel user. This is a really easy one-line way in the code to set this up.

[stextbox id=’info’]
Set Rnge = Selection
[/stextbox]

Turn Off Automatic Calculations.

This piece of code will turn off automatic calculations in the Excel. Automatic updating and calculations are the default setting for Excel. So, if we want to only update user-selected cells we need to restrict any automatic updating.

[stextbox id=’info’]
Application.Calculation = xlCalculateManual
[/stextbox]

Calculate The User Selected Range.

Now we have turned off the automatic updates, Excel will now update and calculate cells that have been selected by the user.

[stextbox id=’info’]
Rnge.Calculate
[/stextbox]

Turn On Automatic Calculations.

Finally, we can turn automatic calculations back on with this small line of code. This returns Excel to its default setting.

[stextbox id=’info’]
Application.Calculation = xlCalculateAuto
[/stextbox]

Ending The Macro.

The routine then ends with the End Sub piece of code.  This was already entered into the module for us when started the type the name of the Macro.

[stextbox id=’info’]
End Sub
[/stextbox]

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below

 

How To Excel At Excel – Macro Mondays Blog Posts.

 

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.