[Excel VBA] How to Extract and Copy Only Specific Columns

2025年12月29日

Overview

When processing Excel data, you often need to extract only specific columns instead of the entire dataset. While you can do this manually using filters or copy-paste, VBA allows you to automate the extraction of multiple columns at once.

This article introduces a method to specify column numbers using an array and copy them efficiently to another location.

Sample Code

Sub ExtractSelectedColumns()
    Dim srcRange As Range
    Dim colList As Variant
    Dim i As Long
    
    ' Define the source range
    Set srcRange = Range("B2:F11")
    
    ' Define column numbers to extract (Relative position within the range)
    colList = Array(1, 4) ' Extract the 1st and 4th columns
    
    ' Copy each column
    For i = 0 To UBound(colList)
        srcRange.Columns(colList(i)).Copy _
            Destination:=Range("H2").Offset(0, i)
    Next i
End Sub

Code Explanation

1. Specify the Source Range (srcRange)

The code targets the range B2:F11.

2. Set Extraction Columns with an Array (colList)

Array(1, 4) specifies the columns to extract. Note that these numbers are relative to the srcRange. The 1st column is the far left of the selected range.

3. Loop and Copy

The code uses srcRange.Columns(colList(i)).Copy to copy the specified columns one by one in a loop.

4. Adjust Destination with Offset

Range("H2").Offset(0, i) ensures that the copied columns are pasted side-by-side, shifting the destination column for each iteration of the loop.

Application Examples

Extracting only “Product Name” and “Sales Amount” from a large sales table.
Pulling “Name” and “Phone Number” from a customer database.
extracting only specific question items from survey results.

Summary

Using arrays in VBA allows you to efficiently automate the extraction of multiple columns. This method is highly reusable because you can flexibly change the extracted content simply by modifying the numbers in the array.

よかったらシェアしてね！