VBA & Macro Problems

How to Sum Nonadjacent Excel Columns Using VBA

Maira Mehtab Sep 22, 2026 871 views

Question details

The user wants to write a VBA macro to calculate the sum of specific, non-contiguous columns in a worksheet, rather than processing a single column or an entire continuous range.

Product: Excel
Device & OS: not provided
Scenario: Automating column summation for reports where the targeted columns for the totals (such as columns 4, 6, 7, and 8) are not located next to each other.
Observed behavior: Needs a dynamic VBA approach to loop through a specific set of column indices and place the sum below the last row of data in those respective columns.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled in your ribbon to access the Visual Basic Editor.

Solution 1Recommended

Use a Variant Array for Explicitly Selected Columns

This method is best when the columns you need to sum are spaced irregularly (for example, columns 4, 6, 7, and 8).

By declaring a Variant variable, you can iterate through an explicitly defined array of column numbers. This ensures only the exact columns you specify are calculated.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications Editor, then click Insert > Module to create a new script area.

Declare Variables

Type `Dim iCol As Variant` and define your last row dynamically, for example: `Lastrow = Cells(Rows.Count, 1).End(xlUp).Row`.

Write the Array Loop

Initiate the loop using the Array function: `For Each iCol In Array(4, 6, 7, 8)`. This tells VBA to only target columns D, F, G, and H.

Apply the Sum Function

Inside the loop, insert the formula to sum from row 2 down to the last row, and place it three rows below the data: `Cells(Lastrow + 3, iCol) = Application.WorksheetFunction.Sum(Range(Cells(2, iCol), Cells(Lastrow, iCol)))`.

Run the Macro

Close the loop with `Next iCol`. Press F5 or click the Run button to execute the code and instantly generate the sums.

Custom Output Placement: The code `Cells(Lastrow + 3, iCol)` places the result three rows below the last used row. You can adjust the '+ 3' to format your layout as needed.

Solution 2

Use a Step Loop for Evenly Spaced Columns

Ideal when the columns you want to sum follow a consistent, evenly spaced pattern (such as every other column: 4, 6, and 8).

Advanced Macro Support in WPS

Automate Your Data Tasks with WPS Spreadsheet

WPS Office provides robust support for VBA macros. You can run the exact same VBA code to sum nonadjacent columns seamlessly, saving you time without needing expensive software.

1. Open your file in WPS: Launch WPS Spreadsheet and open your dataset.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon.
3. Open the VBA Editor: Click 'Visual Basic' or press Alt + F11 to open the macro environment.
4. Run your Macro: Paste your custom array or step loop VBA code and click the 'Run' icon.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) macro formats.Built-in VBA editor included in WPS Office for seamless macro execution.Lightweight application with fast performance for complex data processing.Free and intuitive interface for both beginners and advanced users.

microsoft office alternative - wps office

Frequently Asked Questions

How do I find the Lastrow dynamically in VBA?

You can find the last row dynamically by assigning a variable with this code: `Lastrow = Cells(Rows.Count, 1).End(xlUp).Row`. Ensure you replace '1' with the column number that is guaranteed to have data to the very bottom.

Why am I getting an error on Application.WorksheetFunction.Sum?

This error typically occurs if the range referenced contains error values (such as #N/A or #VALUE!) or if the calculated range is invalid (for example, if Lastrow is less than 2). Verify your data range does not contain errors before running the macro.

Can I format the total cells in VBA after calculating the sum?

Yes. Inside your loop, immediately after the line that assigns the sum to the cell, you can add formatting properties. For example, `Cells(Lastrow + 3, iCol).Font.Bold = True` will make the sum bold.

How do I sum multiple specific non-adjacent cells instead of whole columns?

To sum isolated non-adjacent cells, you can pass multiple ranges to the sum function separated by commas. For example: `Application.WorksheetFunction.Sum(Range("A1"), Range("C3"), Range("E5"))`.

Excel & Data Problems