Calculate Row Major Order in Excel VBA Code Example

Calculate Row Major Order in Excel VBA Code Example

CalculateRowMajorOder

Calculate Row Major Order connects to calculation orders in Excel. There are three types of Calculations Mode which can manipulate calculation environment within a sheet or workbook. What if you wish to calculate specific row in a bulk data? CalculateRowMajorOrder method exposed by Range object allows user to recalculate selected row in excel.

Syntax

expression.CalculaterowMajorOrder

Example

Set the Excel Calculate Mode to Manual by following below steps:

  1. Navigate Formula tab
  2. Locate Calculation Options dropdown button
  3. Select Manual

Once Calculation Modes set to Manual you can try drill down or copy paste any formula as shown below and notice the formulas are dragged and pointing to the correct references but formula result sticks to the base formula cell which we dragged. This is because of manual mode, where formulas are dragged but not calculated as we set the calculation mode to manual. Select a specific range as shown below where formula dragged from B3 to E3

Code example

Public Sub CodeExampleCalculateRowMajorOrder()
    
    'Declare range object
    Dim oRange As Range
    
    'Bind selection
    Set oRange = Selection
    
    'Force calculation to manual mode
    Application.Calculation = xlCalculationManual
    
    'Calculate only selected row
    oRange.CalculateRowMajorOrder
    
    'memory cleanup
    Set oRange = Nothing
End Sub

Output

Notice the output above where only selected row got calculated but formulas dragged at row number four still in manual calculation mode.

Leave a Reply

Your email address will not be published.