VBA & Macro Problems

How to Recalculate Excel Formulas After a Dropdown Selection Using VBA

Muhammad TalhaMuhammad Talha Nov 10, 2026 869 views

Question details

Use VBA to automatically recalculate specific formula columns when a dropdown menu selection is changed in a workbook set to manual calculation.

How to Recalculate Excel Formulas After a Dropdown Selection Using VBA
Product
Excel
Device & OS
not provided
Scenario
The user's workbook is slow, so automatic calculation is disabled. They need formulas in columns AR through AT to recalculate automatically via VBA whenever the dropdown selection in cell G2 changes, working alongside existing filtering code.
Observed behavior
Without a specific macro command, the formulas in the target columns do not update when the dropdown value changes due to automatic calculation being disabled.
Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have basic familiarity with the VBA Editor and Worksheet events.

Solution 1Recommended

Add a Range Calculation Command to the Worksheet_Change Event

Insert a specific VBA calculate command within the worksheet's event code to trigger recalculation only for the required cells when the dropdown is modified.

Since calculating the entire workbook is slow, you can use VBA to calculate only the specific columns containing the formulas you need updated. By utilizing the Me.Range.Calculate method within the Worksheet_Change event, Excel will refresh only the specified range, maintaining optimal workbook performance.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) Editor.

2
Locate the Target Worksheet Code

In the Project Explorer pane on the left, double-click the specific worksheet containing your G2 dropdown and existing filtering code.

3
Insert the Range Calculate Code

Locate your Worksheet_Change procedure. Right before the 'End Sub' line, add the code: Me.Range("AR:AT").Calculate. If you want to calculate the whole sheet instead, you can simply use Me.Calculate.

4
Save and Test

Save the VBA code, return to your spreadsheet, and change the dropdown selection in cell G2. Verify that the formulas in columns AR through AT instantly update.

Add a Range Calculation Command to the Worksheet_Change Event
Performance Optimization: Using Me.Range("AR:AT").Calculate is highly recommended over calculating the entire workbook or worksheet, as it drastically reduces processing time in heavy, complex files.
Advanced Spreadsheet Performance

Manage Macros and Calculations Efficiently with WPS Spreadsheet

WPS Spreadsheet offers robust support for VBA macros and advanced formulas, allowing you to easily handle complex automated tasks like partial recalculations in heavy data files.

  1. 1. Open Your Macro File: Download and launch WPS Office, then open your .xlsm file in WPS Spreadsheet.
  2. 2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon and click on 'VBA Editor'.
  3. 3. Edit the Worksheet Event: Paste your Me.Range("AR:AT").Calculate command into the relevant sheet's code module.
  4. 4. Test the Automation: Close the editor and change your dropdown selection to see the targeted formulas recalculate instantly.
Fully compatible with Microsoft Excel Macro-Enabled workbooks (.xlsm).Built-in VBA editor for seamless macro creation, debugging, and execution.Lightweight software architecture handles large, calculation-heavy workbooks with ease.Intuitive user interface for managing automatic and manual calculation settings.
microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA calculation code not running when I change the dropdown?

This usually happens if macro security settings are blocking execution or if 'Application.EnableEvents' was accidentally set to False by another macro. Ensure macros are enabled and run 'Application.EnableEvents = True' in the Immediate Window to restore event triggering.

Can I use VBA to recalculate multiple non-adjacent ranges?

Yes, you can calculate non-contiguous ranges by chaining them in your VBA command. For example, use Me.Range("A1:A10, C1:C10, AR:AT").Calculate to update specific detached columns at once.

How do I calculate the entire worksheet instead of just columns AR to AT?

To recalculate all formulas on the active worksheet rather than a specific range, replace the range command with 'Me.Calculate' or 'ActiveSheet.Calculate' within the Worksheet_Change event.