How to Loop Through Non-Adjacent Columns in Excel VBA
Question details
The user needs to process specific rows in two separate, non-consecutive columns using Excel VBA without affecting the columns in between.
- Product
- Excel VBA
- Device & OS
- not provided
- Scenario
- Writing a VBA macro to iterate through specific cells in separate columns, such as Column A and Column L, for rows 89 through 103.
- Observed behavior
- Using standard range syntax incorrectly selects the entire rectangular block spanning all columns between the two references. The goal is to isolate the loop strictly to the specified non-adjacent columns.
Ensure you have the Developer tab enabled in Excel and have a basic understanding of writing VBA macros in the Visual Basic Editor.
Use a Comma-Separated Multiple-Area Range Syntax
Define the range using a comma within the address string to explicitly create two separate areas for the loop.
When you use a comma-separated string like Range("A89:A103, L89:L103"), Excel creates a multiple-area range. This prevents the macro from processing the columns in between.
Alternatively, using two separate string arguments like Range("A89:A103", "L89:L103") instructs Excel to draw a box from the top-left coordinate to the bottom-right coordinate, unintentionally including columns B through K.
Press ALT + F11 to open the VBA Editor in Excel, then insert or open your target module.
Declare your range variable and set it using the single-string, comma-separated syntax. For example: Set rngOccasion = Range("A89:A103, L89:L103").
Write a For Each loop to iterate through the cells: For Each cl In rngOccasion.Cells.
Insert your desired code to process each cell (referenced as cl), then close the loop by typing Next cl.
Write and Execute VBA Macros Effortlessly in WPS Office
WPS Spreadsheet provides excellent compatibility with Excel VBA, allowing you to run your multiple-area range loops seamlessly. You can edit, debug, and execute macros with familiar syntax.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled workbook.
- 2. Access the Developer Tools: Go to the Developer tab on the ribbon and click on 'VBA Editor' to launch the coding environment.
- 3. Run Your Code: Insert your non-adjacent range loop code and press F5 to execute it directly within WPS Spreadsheet.
Frequently Asked Questions
How do I loop through each area of a multiple-area range separately?
You can nest your loops. First, loop through the areas using 'For Each rngArea In rngOccasion.Areas', and then loop through the cells within that specific area using 'For Each cl In rngArea.Cells'.
Why is my VBA loop processing the columns I wanted to skip?
This happens if you use the syntax Range("A89:A103", "L89:L103"). Excel interprets this as the top-left and bottom-right corners of a single rectangle, effectively selecting all columns from A to L. Always use a single string with a comma Range("A89:A103, L89:L103") to keep them separate.
Can I select more than two non-adjacent columns using this method?
Yes, you can add as many ranges as you need within the same string, separated by commas. For example: Range("A1:A10, C1:C10, E1:E10") will successfully loop through all three non-adjacent areas.
