VBA excel vba select multiple columns not in sequential order vba select non-adjacent columns excel vba select multiple columns excel vba multi column selection

Excel VBA: Select Multiple Columns Not in Sequential Order — Complete Guide (2026)

Vizan Patel

May 16, 2026

Excel VBA: Select Multiple Columns Not in Sequential Order — Complete Guide (2026)

Why Non-Sequential Column Selection Matters in VBA

Before diving into the code, let's look at why this comes up so often in real Excel automation work:

Formatting — applying bold, color, or borders to specific columns like A, C, F, and H
Hiding/Showing — hiding non-essential columns while keeping key data visible
Copying — extracting specific columns from a large dataset into a new sheet
Deleting — removing certain columns while keeping others intact
Printing — setting a print area that includes only specific non-adjacent columns
Data export — pulling selected columns into a CSV or another workbook

All of these require selecting non-sequential columns programmatically — and VBA gives you several powerful ways to do it.

Method 1 — Select Non-Sequential Columns Using Range With Comma Separator

The simplest way to select multiple non-adjacent columns in VBA is to use the Range object with column letters separated by commas — exactly like the multi-select syntax in Excel formulas.

vba

Sub SelectNonSequentialColumns()
    ' Select columns A, D, and G (non-sequential)
    Range("A:A, D:D, G:G").Select
End Sub

Or using the shorthand column notation:

vba

Sub SelectColumnsShorthand()
    ' Same result — cleaner syntax
    Columns("A:A,D:D,G:G").Select
End Sub

Applying formatting to non-sequential columns:

vba

Sub FormatNonSequentialColumns()
    With Range("A:A, C:C, F:F, H:H")
        .Font.Bold = True
        .Interior.Color = RGB(198, 224, 180)  ' Light green background
        .Font.Color = RGB(0, 97, 0)           ' Dark green text
    End With
End Sub

Key point: You don't always need to .Select first — in most cases you can apply actions directly to the Range object using With blocks, which is faster and more efficient.

Best used for: Simple, fixed column selections where you know the column letters in advance.

Method 2 — Select Non-Sequential Columns Using the Union Method

The Union method is the most flexible and professional way to combine non-adjacent column ranges in VBA. It merges multiple separate Range objects into one combined range.

vba

Sub SelectWithUnion()
    Dim col1 As Range
    Dim col2 As Range
    Dim col3 As Range
    Dim combinedRange As Range
    
    ' Define each column range separately
    Set col1 = Columns("A")
    Set col2 = Columns("D")
    Set col3 = Columns("G")
    
    ' Combine them using Union
    Set combinedRange = Union(col1, col2, col3)
    
    ' Now select or act on the combined range
    combinedRange.Select
End Sub

Union with direct column references:

vba

Sub UnionDirect()
    Dim combined As Range
    Set combined = Union(Columns(1), Columns(4), Columns(7), Columns(10))
    
    ' Hide all four non-sequential columns at once
    combined.EntireColumn.Hidden = True
End Sub

Why Union is better than Range with commas for complex scenarios:

Works with column numbers (not just letters) — great for dynamic selection
Can combine ranges from calculations or loops
Easier to read and maintain in long macros
Works reliably even when column references are built dynamically

Best used for: Dynamic column selection, loops, situations where column references are calculated at runtime.

Method 3 — Select Non-Sequential Columns by Column Number

When working with large datasets, it's often easier to reference columns by number rather than by letter. VBA's Columns(n) syntax makes this straightforward.

vba

Sub SelectByColumnNumber()
    ' Select columns 1 (A), 5 (E), and 9 (I) — non-sequential
    Dim combined As Range
    Set combined = Union(Columns(1), Columns(5), Columns(9))
    combined.Select
End Sub

Convert between column letters and numbers:

vba

Sub ColumnLetterToNumber()
    ' Get column number from letter
    Dim colNum As Integer
    colNum = Range("D1").Column  ' Returns 4
    MsgBox "Column D is number: " & colNum
End Sub

Sub ColumnNumberToLetter()
    ' Get column letter from number
    Dim colLetter As String
    colLetter = Split(Cells(1, 7).Address, "$")(1)  ' Returns "G" for column 7
    MsgBox "Column 7 is letter: " & colLetter
End Sub

Best used for: Data processing macros where column positions are calculated dynamically based on header names or data patterns.

Method 4 — Select Non-Sequential Columns From an Array

When you have a list of column numbers stored in an array — perhaps built from user input or a configuration setting — you can loop through the array to build a Union selection.

vba

Sub SelectColumnsFromArray()
    ' Define which columns to select (by number)
    Dim colNumbers() As Integer
    colNumbers = Array(1, 3, 5, 8, 12)  ' Columns A, C, E, H, L
    
    Dim combined As Range
    Dim i As Integer
    
    ' Build the Union from the array
    For i = 0 To UBound(colNumbers)
        If combined Is Nothing Then
            Set combined = Columns(colNumbers(i))
        Else
            Set combined = Union(combined, Columns(colNumbers(i)))
        End If
    Next i
    
    ' Select all non-sequential columns at once
    combined.Select
    
    MsgBox "Selected " & UBound(colNumbers) + 1 & " non-sequential columns."
End Sub

Practical example — select columns by header name:

vba

Sub SelectColumnsByHeaderName()
    ' Select columns whose headers match a list of names
    Dim targetHeaders() As String
    targetHeaders = Array("First Name", "Email", "Amount", "State")
    
    Dim combined As Range
    Dim headerRow As Range
    Dim cell As Range
    Dim i As Integer
    
    Set headerRow = Rows(1)  ' Assumes headers are in row 1
    
    For i = 0 To UBound(targetHeaders)
        For Each cell In headerRow.Cells
            If cell.Value = targetHeaders(i) Then
                If combined Is Nothing Then
                    Set combined = cell.EntireColumn
                Else
                    Set combined = Union(combined, cell.EntireColumn)
                End If
                Exit For
            End If
        Next cell
    Next i
    
    If Not combined Is Nothing Then
        combined.Select
        MsgBox "Selected columns: " & combined.Address
    Else
        MsgBox "No matching column headers found.", vbExclamation
    End If
End Sub

This is extremely useful for USA business users working with CRM exports, financial reports, or any dataset where column positions might change but header names stay the same.

Best used for: Header-based column selection, user-configurable column picks, dynamic datasets.

Method 5 — Select Non-Sequential Columns Using a Loop With Step

When you need to select every Nth column — for example, every other column, or every third column — a loop with a Step value is the cleanest approach.

vba

Sub SelectEveryOtherColumn()
    ' Select every other column from 1 to 20 (columns 1, 3, 5, 7... 19)
    Dim combined As Range
    Dim i As Integer
    
    For i = 1 To 20 Step 2
        If combined Is Nothing Then
            Set combined = Columns(i)
        Else
            Set combined = Union(combined, Columns(i))
        End If
    Next i
    
    combined.Select
End Sub

Sub SelectEveryThirdColumn()
    ' Select every 3rd column from column 1 to 30
    Dim combined As Range
    Dim i As Integer
    
    For i = 1 To 30 Step 3
        If combined Is Nothing Then
            Set combined = Columns(i)
        Else
            Set combined = Union(combined, Columns(i))
        End If
    Next i
    
    combined.Interior.Color = RGB(255, 242, 204)  ' Yellow highlight
End Sub

Best used for: Pattern-based column selection, alternating column formatting, reports with repeating column structures.

Method 6 — Copy Non-Sequential Columns to a New Sheet

One of the most practical applications of non-sequential column selection is copying specific columns from a large dataset to a new sheet — for reporting or data export purposes.

vba

Sub CopyNonSequentialColumnsToNewSheet()
    Dim sourceSheet As Worksheet
    Dim destSheet As Worksheet
    Dim combined As Range
    Dim lastRow As Long
    
    Set sourceSheet = ThisWorkbook.Sheets("RawData")
    
    ' Create a new destination sheet
    On Error Resume Next
    ThisWorkbook.Sheets("Export").Delete
    On Error GoTo 0
    
    Set destSheet = ThisWorkbook.Sheets.Add
    destSheet.Name = "Export"
    
    ' Select non-sequential columns A, C, E, H from source
    With sourceSheet
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        Set combined = Union( _
            .Range("A1:A" & lastRow), _
            .Range("C1:C" & lastRow), _
            .Range("E1:E" & lastRow), _
            .Range("H1:H" & lastRow))
    End With
    
    ' Copy and paste to destination sheet
    combined.Copy
    destSheet.Range("A1").PasteSpecial xlPasteValues
    
    Application.CutCopyMode = False
    destSheet.Columns.AutoFit
    
    MsgBox "Non-sequential columns copied to 'Export' sheet successfully!"
End Sub

Method 7 — Hide or Show Non-Sequential Columns

Hiding and showing specific non-adjacent columns is one of the most common dashboard automation tasks.

vba

Sub HideNonSequentialColumns()
    ' Hide columns B, D, F, and I (non-sequential)
    Union(Columns("B"), Columns("D"), Columns("F"), Columns("I")).Hidden = True
End Sub

Sub ShowNonSequentialColumns()
    ' Show the same columns again
    Union(Columns("B"), Columns("D"), Columns("F"), Columns("I")).Hidden = False
End Sub

Sub ToggleColumns()
    ' Toggle visibility of non-sequential columns with one macro
    Dim targetCols As Range
    Set targetCols = Union(Columns("B"), Columns("D"), Columns("F"), Columns("I"))
    targetCols.Hidden = Not targetCols.Hidden
End Sub

Add a button to toggle columns: Assign the ToggleColumns macro to a button on your sheet so users can show/hide columns with one click — no VBA knowledge required on their end.

Method 8 — Delete Non-Sequential Columns Safely

Deleting non-adjacent columns requires a special approach — you must delete from right to left, otherwise column positions shift and you end up deleting the wrong columns.

vba

Sub DeleteNonSequentialColumns()
    ' IMPORTANT: Always delete from right to left
    ' Deleting left columns first shifts positions of right columns
    
    ' Wrong way (deletes wrong columns):
    ' Columns(2).Delete  ' deletes B, then C shifts to B position
    ' Columns(5).Delete  ' now deletes wrong column
    
    ' Right way — sort column numbers descending first:
    Dim colsToDelete() As Integer
    colsToDelete = Array(10, 7, 4, 2)  ' Already in descending order
    
    Dim i As Integer
    For i = 0 To UBound(colsToDelete)
        Columns(colsToDelete(i)).Delete
    Next i
    
    MsgBox "Columns deleted successfully."
End Sub

Best used for: Cleaning up imported data, removing unnecessary columns before sharing a file.

Common Mistakes When Selecting Non-Sequential Columns in VBA

Mistake 1 — Using Select when you don't need to In VBA, you rarely need to .Select a range before acting on it. This is slower and causes screen flicker. Instead, act on the range directly:

vba

' Slow way
Columns("A:A,D:D,G:G").Select
Selection.Font.Bold = True

' Fast way (no Select needed)
Range("A:A,D:D,G:G").Font.Bold = True

Mistake 2 — Forgetting the Union initializer check When building a Union in a loop, the first iteration must use Set combined = Columns(i) not Union(Nothing, Columns(i)). Always check If combined Is Nothing before calling Union.

Mistake 3 — Deleting columns left to right Always delete columns from right to left (highest column number first) to prevent position shifting.

Mistake 4 — Using hardcoded column letters in dynamic datasets If your data might have columns added or removed, use header-name-based selection (Method 4) instead of hardcoded letters like Columns("D").

Mistake 5 — Not turning off ScreenUpdating When selecting and formatting many non-adjacent columns, add Application.ScreenUpdating = False at the start and True at the end to prevent visible flickering.

Frequently Asked Questions

How do I select multiple non-sequential columns in Excel VBA?

Use the Union method: Set combined = Union(Columns(1), Columns(4), Columns(7)) then combined.Select. Or use Range with comma-separated column references: Range("A:A, D:D, G:G").Select.

How do I select columns by name (header) in VBA?

Loop through row 1 to find cells matching your target header names, then use Union to combine their EntireColumn ranges. Full code is shown in Method 4 of this guide.

Can I select non-adjacent columns using column numbers instead of letters?

Yes — use Columns(n) where n is the column number: Union(Columns(1), Columns(5), Columns(9)).Select selects columns A, E, and I.

How do I copy non-sequential columns to a new sheet in VBA?

Build a Union of the column ranges you need, call .Copy on the Union, then use .PasteSpecial xlPasteValues on the destination sheet's starting cell.

How do I hide multiple non-adjacent columns in VBA?

Use Union(Columns("B"), Columns("D"), Columns("F")).Hidden = True — no Select needed.

Why does deleting non-sequential columns give wrong results in VBA?

Because deleting a column shifts all columns to its right. Always delete from right to left — highest column number first — to avoid position shifting errors.

How do I select every other column in VBA?

Use a For loop with Step 2: For i = 1 To 20 Step 2 — build a Union of every other column using Union inside the loop. Full code is in Method 5 of this guide.

How do I apply formatting to non-sequential columns without selecting them?

Use With Range("A:A, C:C, F:F") and apply formatting inside the With block. You never need to select first — acting on the range directly is faster and avoids screen flickering.

Quick Reference — Non-Sequential Column Selection Cheat Sheet

Final Thoughts

Selecting multiple non-sequential columns in Excel VBA is a skill that unlocks a huge range of automation possibilities — from smart formatting and dynamic reporting to clean data exports and one-click dashboard updates.

The most important things to remember:

Use Union for dynamic, programmatic column selection — it's the most flexible method
Use Range with commas for simple, fixed selections — it's the quickest to write
Never select when you don't need to — act on ranges directly for better performance
Delete right to left when removing non-adjacent columns
Use header-name-based selection for datasets where column positions might change

Don't want to write VBA yourself? Try our free tools:

🔗 VBA Macro Generator — describe what you need in plain English, get working VBA code instantly
🔗 AI Excel Assistant — ask any VBA question and get instant answers
🔗 Merge Excel Files — combine multiple workbooks without any code
🔗 Split Excel Files — split workbooks by sheet instantly
🔗 Formula Fixer — fix broken Excel formulas automatically with AI