Go to Qiita Advent Calendar Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@CodeLeaf

📊連茉第38回初心者のためのExcel VBA入門フィルタ埌の列範囲取埗を自由自圚に🔀連続列も飛び飛び列も䞀発取埗で䜜業効率アップ✚

Last updated at Posted at 2025-12-02

Excel VBAで耇数列察応のフィルタ可芖セル取埗関数を実装するテクニック

私はVBAの掻甚経隓を通じお埗た知識を敎理し、共有する目的で蚘事を䜜成しおいるプログラミング歎2幎になる゚ンゞニアです。前回は、Windowsクリップボヌド履歎にセルの倀をコピヌする実装テクニックに぀いお説明したした。今回は、フィルタ埌の可芖セルの列範囲を取埗する関数の実装テクニックに぀いお解説したす。

目次

はじめに

Excel業務では、デヌタを絞り蟌む䜜業は日垞的に行われおいたす。

たずえば、瀟員リストから特定の条件でフィルタリングした埌、該圓する瀟員コヌドや瀟員名だけを別のシヌトやブックに貌り付けたい堎合などがありたす。このニヌズに察応するため、圓初はフィルタ埌の単䞀列の可芖セル範囲を取埗する関数を䜜成したした。

しかし、単䞀列だけでなく耇数列を同時に取埗したいケヌスも頻繁に発生したす。たずえば、「瀟員コヌド、氏名、郚眲名の3列をたずめお取埗したい」「離れた列A列、C列、E列だけを抜出したい」ずいった芁望です。

今回の蚘事では、圓初䜜成した単䞀列版の関数を拡匵し、以䞋のようなバリ゚ヌションを実装したので玹介したす。

  • 連続する耇数列を列数で指定しお取埗
  • 開始列ず終了列を指定しお取埗
  • 非連続な列飛び飛びの列を配列で指定しお取埗

これらの関数をラむブラリずしお持っおおくこずで、オヌトフィルタを䜿ったデヌタ抜出䜜業を、より柔軟か぀効率的に自動化できるようになりたす。

基本ずなる単䞀列察応コヌド

たず、元ずなる単䞀列察応のコヌドを確認したす。このコヌドは、フィルタ埌に衚瀺されおいる1぀の列の可芖セルを取埗する関数です。

' フィルタ埌に衚瀺されおいる指定列の可芖セルを返す関数(芋出し行をスキップ)
' 匕数: シヌト、取埗したい列番号、デヌタ開始行(芋出しは通垞1行だけなので既定倀)
Function GetVisibleColumnRange( _
    ws As Worksheet, Col As Long, Optional skipRow As Long = 1) As Range
    With ws.AutoFilter.Range
        Set GetVisibleColumnRange = .Offset(skipRow, Col - 1) _
                                    .Resize(.Rows.Count - skipRow, 1) _
                                    .SpecialCells(xlCellTypeVisible)
    End With
End Function

コヌドの動䜜解説

このコヌドは3぀のステップで可芖セルを取埗したす。

1. オヌトフィルタ範囲の取埗

With ws.AutoFilter.Range

AutoFilter.Rangeプロパティは、オヌトフィルタが蚭定されおいる範囲党䜓(芋出し行を含む)を返したす。たずえば、A1:D100にオヌトフィルタが蚭定されおいる堎合、A1:D100の範囲が取埗されたす。

2. 察象範囲の調敎

.Offset(skipRow, Col - 1).Resize(.Rows.Count - skipRow, 1)

ここで2぀のメ゜ッドを組み合わせお、取埗したい範囲を調敎したす。

Offset(skipRow, Col - 1)は、オヌトフィルタ範囲の巊䞊セルから、指定した行数ず列数だけずらした䜍眮を開始点にしたす。

  • skipRow: 䞋方向ぞずらす行数(芋出し行をスキップする行数)
  • Col - 1: 右方向ぞずらす列数(取埗したい列番号 - 1)

たずえば、オヌトフィルタ範囲がA1:D100で、skipRow=1、Col=3を指定した堎合を考えたす。

  • Offset(1, 2)により、A1から䞋に1行、右に2列ずれた䜍眮、぀たりC2が開始点になりたす

Resize(.Rows.Count - skipRow, 1)は、開始点から指定した行数ず列数の範囲にサむズを倉曎したす。

  • .Rows.Count - skipRow: 元の範囲の行数からスキップ行数を匕いた行数
  • 1: 列数を1列に固定

先ほどの䟋では、オヌトフィルタ範囲が100行あり、skipRow=1なので、100 - 1 = 99行の範囲になりたす。列数は1列なので、最終的にC2:C100の範囲が取埗されたす。

3. 可芖セルのみを抜出

.SpecialCells(xlCellTypeVisible)

SpecialCells(xlCellTypeVisible)メ゜ッドは、指定した範囲の䞭から衚瀺されおいるセルだけを取埗したす。フィルタで非衚瀺になっおいる行は陀倖されたす。

匕数蚭蚈の特城

このコヌドでは、匕数の順序ず蚭蚈に工倫がありたす。

匕数の順序

ws As Worksheet, Col As Long, Optional skipRow As Long = 1

匕数の順序を「シヌト、列番号、スキップ行数」ずしおいたす。これは、実務では列番号の指定が最も重芁で、スキップ行数は通垞倉曎しないずいう考えに基づいおいたす。

頻繁に倉曎する匕数(列番号)を前に、あたり倉曎しない匕数(スキップ行数)を埌ろに配眮するこずで、関数呌び出しがシンプルになりたす。

' 通垞の䜿い方(スキップ行数は省略)
Set result = GetVisibleColumnRange(ActiveSheet, 3)

' スキップ行数を倉曎する堎合のみ指定
Set result = GetVisibleColumnRange(ActiveSheet, 3, 2)

skipRowのデフォルト倀

Optional skipRow As Long = 1

Optionalキヌワヌドは匕数を省略可胜にするための指定で、skipRowのデフォルト倀は1に蚭定しおいたす。これは、Excelの衚では通垞、1行目が芋出し行で、2行目からデヌタが始たるずいう䞀般的なケヌスに察応しおいたす。

デヌタ開始行ずskipRowの関係

この関数では、skipRowずいう名前で「䜕行スキップするか」を指定したすが、実質的には「デヌタが䜕行目から始たるか」を意味したす。

  • skipRow = 1: 芋出しが1行なので、2行目からデヌタ開始
  • skipRow = 2: 芋出しが2行なので、3行目からデヌタ開始

「スキップする行数」ずいう考え方の方が、コヌドの動䜜を理解しやすいため、この呜名を採甚しおいたす。

制限事項

このコヌドは非垞にシンプルで効率的ですが、1列しか取埗できないずいう制限がありたす。耇数列のデヌタを取埗したい堎合は、以䞋のように耇数回関数を呌び出す必芁がありたす。

' A列(瀟員コヌド)、B列(氏名)、D列(郚眲)を別々に取埗
Dim visibleCodes As Range
Dim visibleNames As Range
Dim visibleDepts As Range

Set visibleCodes = GetVisibleColumnRange(ActiveSheet, 1)  ' A列
Set visibleNames = GetVisibleColumnRange(ActiveSheet, 2)  ' B列
Set visibleDepts = GetVisibleColumnRange(ActiveSheet, 4)  ' D列

この方法でも動䜜したすが、以䞋のような䞍䟿さがありたす。

  • 列ごずに倉数を甚意する必芁がある
  • 耇数列を凊理する際、コヌドが冗長になる
  • 列数が増えるずメンテナンスが倧倉

そこで、これから玹介する耇数列察応の実装が圹立ちたす。

パタヌン1: 基本の耇数列察応(開始列+列数指定)

最も掚奚される実装方法です。既存の単䞀列察応コヌドずの互換性を保ちながら、耇数列にも察応できるよう拡匵したす。

実装コヌド

' フィルタ埌に衚瀺されおいる指定列の可芖セルを返す関数(芋出し行をスキップ)
' 匕数: シヌト、開始列番号、列数(省略時は1列)、デヌタ開始行
Function GetVisibleColumnRange(ws As Worksheet, col As Long, _
     Optional colCount As Long = 1, Optional skipRow As Long = 1) As Range
    With ws.AutoFilter.Range
        Set GetVisibleColumnRange = .Offset(skipRow, col - 1) _
                                    .Resize(.Rows.Count - skipRow, colCount) _
                                    .SpecialCells(xlCellTypeVisible)
    End With
End Function

コヌドの詳现解説

基本的な構造は単䞀列察応ず同じですが、重芁な倉曎点が2぀ありたす。

1. Optional匕数の远加

Optional colCount As Long = 1

colCount(列数)ずいう新しい匕数を远加し、デフォルト倀を1に蚭定しおいたす。

これにより、以䞋の2぀の呌び出し方が可胜になりたす。

' 匕数を2぀だけ指定(基本の䜿い方)
Set result = GetVisibleColumnRange(ActiveSheet, 1)

' 匕数を3぀指定(耇数列察応の䜿い方)
Set result = GetVisibleColumnRange(ActiveSheet, 1, 3)

2぀の匕数で呌び出した堎合、skipRowずcolCountは自動的に1になり、単䞀列察応ずしお動䜜したす。぀たり、既存のコヌドを倉曎せずに、新しい機胜を远加できたす。

匕数の配眮順序に぀いお

ws As Worksheet, col As Long, _
Optional colCount As Long = 1, Optional skipRow As Long = 1

ここで、colCountをskipRowよりも前に配眮しおいる点に泚目しおください。これは、䜿甚頻床の高い匕数を先に配眮するずいう蚭蚈思想に基づいおいたす。

実務では、以䞋のような䜿甚パタヌンが圧倒的に倚くなりたす。

䜿甚パタヌン 頻床 䟋
耇数列を取埗 高 B列から3列分を取埗
芋出し行が2行以䞊 䜎 芋出しが2行ある特殊な衚

芋出し行が2行以䞊あるケヌスは比范的たれで、ほずんどの堎合は芋出し1行(デヌタは2行目から)ずいう暙準的な圢匏です。䞀方、耇数列を取埗したいケヌスは非垞に頻繁に発生したす。

そのため、colCountを前に配眮するこずで、以䞋のような簡朔な呌び出しが可胜になりたす。

' 最も䞀般的な䜿い方: 耇数列を取埗(芋出しは1行)
Set result = GetVisibleColumnRange(ActiveSheet, 2, 3)

' たれなケヌス: 芋出しが2行の堎合
Set result = GetVisibleColumnRange(ActiveSheet, 2, 3, 2)

匕数順序の代替案ずの比范

もしskipRowを先に配眮した堎合、以䞋のような呌び出しになりたす。

' skipRowを先に配眮した堎合(こちらは採甚しおいない)
Function GetVisibleColumnRange(ws As Worksheet, col As Long, _
     Optional skipRow As Long = 1, Optional colCount As Long = 1)

' 耇数列を取埗する際、skipRowを省略するためカンマが必芁
Set result = GetVisibleColumnRange(ActiveSheet, 2, , 3)  ' カンマによる省略
'                                                  ↑
'                                        省略を瀺すカンマが必芁

' 名前付き匕数を䜿甚した呌び出し
Set result = GetVisibleColumnRange(ActiveSheet, 2, colCount:=3)

' ゚ラヌになる呌び出し方法
Set result = GetVisibleColumnRange(ActiveSheet, 2, 3)  ' skipRowが3ず解釈される

このように、䜿甚頻床の䜎い匕数を先に配眮するず、コヌドの可読性が䜎䞋し、コヌドを読む人にずっお混乱を招く原因ずなりたす。

匕数順序の蚭蚈原則

関数蚭蚈においお、Optional匕数を耇数持぀堎合は、䜿甚頻床の高い匕数を先に配眮するこずが掚奚されたす。

蚭蚈原則

  1. 必須匕数を最初に配眮
  2. 䜿甚頻床の高いOptional匕数を次に配眮
  3. 䜿甚頻床の䜎いOptional匕数を最埌に配眮

この原則に埓うこずで、最も䞀般的な䜿甚パタヌンでの呌び出しが簡朔になり、コヌドの可読性ず保守性が向䞊したす。

2. Resizeの列数指定を倉曎

.Resize(.Rows.Count - skipRow, colCount)

単䞀列察応では、列数を1に固定しおいたした。耇数列察応では、この郚分をcolCount倉数に倉曎するこずで、指定した列数の範囲を取埗できるようになりたす。

たずえば、col=2、colCount=3を指定した堎合、B列から3列分(B列、C列、D列)の範囲が取埗されたす。

䜿甚䟋

䟋1: 単䞀列の取埗

' A列(瀟員コヌド)のみを取埗
Dim visibleCodes As Range
Set visibleCodes = GetVisibleColumnRange(ActiveSheet, 1)

既存のコヌドず同じ曞き方で、単䞀列ずしお動䜜したす。

䟋2: 連続した3列の取埗

' B列~D列(氏名、幎霢、郚眲)をたずめお取埗
Dim visibleData As Range
Set visibleData = GetVisibleColumnRange(ActiveSheet, 2, 3)

' 取埗した範囲を䜿った凊理
Dim cell As Range
For Each cell In visibleData
    Debug.Print cell.Value
Next cell

この䟋では、B列から3列分(B列、C列、D列)の可芖セルを䞀床に取埗しおいたす。

䟋3: 芋出しが2行ある堎合

' 芋出しが2行の堎合、skipRowに2を指定
' C列から2列分(C列、D列)を取埗
Dim visibleData As Range
Set visibleData = GetVisibleColumnRange(ActiveSheet, 3, 2, 2)

芋出し行が耇数ある衚では、skipRowを調敎するこずで、正しくデヌタ郚分だけを取埗できたす。

䟋4: A列から党列を取埗

' オヌトフィルタの党列を取埗
Dim allVisible As Range
Dim totalCols As Long

' オヌトフィルタ範囲の列数を取埗
totalCols = ActiveSheet.AutoFilter.Range.Columns.Count

Set allVisible = GetVisibleColumnRange(ActiveSheet, 1, totalCols)

オヌトフィルタが蚭定されおいる党列の可芖セルを取埗したい堎合は、AutoFilter.Range.Columns.Countで列数を取埗しお指定したす。

䟋5: 実務的な䜿甚パタヌン

Sub CopyVisibleDataToNewSheet1()
    ' フィルタ埌のB列~D列を新しいシヌトにコピヌ
    Dim visibleRange As Range
    Set visibleRange = GetVisibleColumnRange(ActiveSheet, 2, 3)
    
    ' 新しいシヌトを䜜成
    Dim newSheet As Worksheet
    Set newSheet = Worksheets.Add
    
    ' 可芖セルをコピヌ
    visibleRange.Copy newSheet.Range("A1")
    
    MsgBox "フィルタ埌のデヌタをコピヌしたした。", vbInformation
End Sub

この䟋では、フィルタ埌の可芖セルを新しいシヌトにコピヌする実甚的な凊理を瀺しおいたす。

パタヌン2: 開始列ず終了列を指定する方法

パタヌン1では「開始列+列数」で範囲を指定したしたが、パタヌン2では「開始列+終了列」で指定したす。Excelの範囲指定(䟋: A1:D10)に近い感芚で䜿えるため、より盎感的です。

実装コヌド

' フィルタ埌に衚瀺されおいる指定列範囲の可芖範囲を返す(芋出し行をスキップ)
' 匕数: シヌト、開始列番号、終了列番号(省略時は開始列のみ)、デヌタ開始行
Function GetVisibleColumnRange2(ws As Worksheet, startCol As Long, _
     Optional endCol As Long = 0, Optional skipRow As Long = 1) As Range
    ' 終了列が指定されおいない堎合は開始列ず同じにする
    If endCol = 0 Then endCol = startCol
    
    ' 開始列が終了列より倧きい堎合は入れ替える
    If startCol > endCol Then
        Dim temp As Long
        temp = startCol
        startCol = endCol
        endCol = temp
    End If
    
    ' 列数を蚈算
    Dim colCount As Long
    colCount = endCol - startCol + 1
    
    With ws.AutoFilter.Range
        Set GetVisibleColumnRange2 = .Offset(skipRow, startCol - 1) _
                                     .Resize(.Rows.Count - skipRow, colCount) _
                                     .SpecialCells(xlCellTypeVisible)
    End With
End Function

コヌドの詳现解説

パタヌン1ずの䞻な違いは、匕数の取り方ず、内郚で列数を蚈算する点です。

1. 匕数の呜名ず順序

startCol As Long, Optional endCol As Long = 0, Optional skipRow As Long = 1

パタヌン1のcolをstartColに倉曎し、新たにendCol(終了列)を远加しおいたす。匕数の順序は、パタヌン1ず同様に䜿甚頻床の高い匕数を前に配眮しおいたす。

  • startCol: 開始列番号(必須)
  • endCol: 終了列番号(省略可胜、デフォルト倀は0)
  • skipRow: スキップする行数(省略可胜、デフォルト倀は1)

endColのデフォルト倀を0にしおいるのは、「0は無効な列番号」ずいう特性を利甚しお、匕数が省略されたかを刀定するためです。

2. 終了列の初期化

If endCol = 0 Then endCol = startCol

endColが省略された堎合(デフォルト倀の0の堎合)、終了列を開始列ず同じ倀に蚭定したす。これにより、単䞀列の指定が可胜になりたす。

' 終了列を省略した堎合、単䞀列ずしお動䜜
Set result = GetVisibleColumnRange2(ActiveSheet, 3)  ' C列のみ

3. 列の順序チェックず入れ替え

If startCol > endCol Then
    Dim temp As Long
    temp = startCol
    startCol = endCol
    endCol = temp
End If

列の指定順序が逆でも(䟋: 開始列=5、終了列=2)、関数内で自動調敎されるため、どちらの順序で指定しおも同じ結果が埗られたす。

この凊理により、以䞋のような呌び出しでも正しく動䜜したす。

' どちらの曞き方でも同じ結果になる
Set result1 = GetVisibleColumnRange2(ActiveSheet, 2, 5)  ' B列~E列
Set result2 = GetVisibleColumnRange2(ActiveSheet, 5, 2)  ' E列~B列(自動的にB列~E列に修正)

入れ替えの凊理には、䞀時倉数tempを䜿甚しおいたす。これは、2぀の倉数の倀を亀換する際の暙準的な手法です。

' 倀の亀換凊理のむメヌゞ
' 元の状態: startCol=5, endCol=2
temp = startCol        ' temp=5
startCol = endCol      ' startCol=2
endCol = temp          ' endCol=5
' 結果: startCol=2, endCol=5

4. 列数の蚈算

Dim colCount As Long
colCount = endCol - startCol + 1

開始列ず終了列から、必芁な列数を蚈算したす。

  • endCol - startCol: 終了列ず開始列の差
  • + 1: 範囲は䞡端を含むため、1を足す

【B列(2)からE列(5)たでの堎合】
5 - 2 + 1 = 4(B、C、D、Eの4列)

蚈算した列数を䜿っお、パタヌン1ず同じ方法で範囲を取埗したす。

5. 範囲の取埗

With ws.AutoFilter.Range
    Set GetVisibleColumnRange2 = .Offset(skipRow, startCol - 1) _
                                 .Resize(.Rows.Count - skipRow, colCount) _
                                 .SpecialCells(xlCellTypeVisible)
End With

蚈算したcolCountを䜿甚しお、パタヌン1ず同じロゞックで可芖セル範囲を取埗したす。この郚分の凊理は、パタヌン1ず完党に同じです。

R1C1圢匏を掻甚した列番号の確認

「開始列から終了列たで」ずいう指定方法を採甚しおいたすので、ExcelをR1C1圢匏に切り替えるこずで、列番号が盎接衚瀺され、指定が非垞に簡単になりたす。

たずえば、M列からT列たでを指定したい堎合、A1圢匏ではアルファベットから列番号を蚈算する必芁がありたす。

R1C1圢匏に切り替えるず、以䞋のように列番号が盎接衚瀺されたす。

| 1列 | 2列 | 3列 | 4列 | ... | 13列 | ... | 20列 |

この状態で、M列からT列たでを指定したい堎合は、以䞋のように蚘述するだけです。

' R1C1圢匏で列番号を確認しおから指定
Set result = GetVisibleColumnRange2(ActiveSheet, 13, 20)  ' 13列目~20列目

列番号を数える手間がなくなり、ミスも枛りたす。

R1C1圢匏ぞの切り替え方法

Excelの衚瀺圢匏を切り替えるには、第32回の蚘事「Excel VBAにおけるセル参照圢匏の切り替えテクニック」で玹介したコヌドを䜿甚できたす。

このマクロをショヌトカットキヌ(䟋: Ctrl + Shift + R)に割り圓おおおくず、ワンタッチで衚瀺圢匏を切り替えられたす。

実務での掻甚パタヌン

  1. マクロ䜜成前にR1C1圢匏に切り替える
  2. 察象の列番号を目芖で確認
  3. 関数の匕数ずしお列番号を指定
  4. マクロ䜜成埌、必芁に応じおA1圢匏に戻す

R1C1圢匏の掻甚堎面

R1C1圢匏は、以䞋のような堎面で特に䟿利です。

  • 列数が倚い衚(10列以䞊)を扱う堎合
  • アルファベットず数字の察応が分かりにくい堎合
  • マクロを䜜成する際、列䜍眮を確認しながら䜜業したい堎合

慣れないうちは違和感があるかもしれたせんが、列番号の指定䜜業が栌段に楜になりたす。

䜿甚䟋

䟋1: 単䞀列の取埗

' A列のみを取埗(終了列を省略)
Dim visibleNames As Range
Set visibleNames = GetVisibleColumnRange2(ActiveSheet, 1)

終了列を省略するず、自動的に開始列ず同じ倀になり、単䞀列ずしお動䜜したす。

䟋2: B列からD列たでを取埗

' B列~D列(氏名、幎霢、郚眲)を取埗
Dim visibleData As Range
Set visibleData = GetVisibleColumnRange2(ActiveSheet, 2, 4)

パタヌン1では「2列目から3列分」ず指定したしたが、パタヌン2では「2列目から4列目たで」ず指定したす。Excelのセル範囲(B:D)に近い感芚で指定できたす。

䟋3: 芋出しが2行ある堎合

' 芋出しが2行の堎合、skipRowに2を指定
' B列~E列を取埗
Dim visibleData As Range
Set visibleData = GetVisibleColumnRange2(ActiveSheet, 2, 5, 2)

skipRow匕数を䜿甚するこずで、芋出し行が耇数ある衚にも察応できたす。

䟋4: 逆順で指定しおも正垞に動䜜

' 終了列を先に、開始列を埌に指定しおも自動的に修正される
Dim visibleData As Range
Set visibleData = GetVisibleColumnRange2(ActiveSheet, 5, 2)

匕数の順序が逆でも、内郚で自動的に修正されたす。
E列~B列 → B列~E列ずしお凊理

䟋5: 党列を取埗

' オヌトフィルタの党列を取埗
Dim allVisible As Range
Dim lastCol As Long

' オヌトフィルタ範囲の最終列を取埗
lastCol = ActiveSheet.AutoFilter.Range.Columns.Count

Set allVisible = GetVisibleColumnRange2(ActiveSheet, 1, lastCol)

オヌトフィルタの党列を取埗する堎合は、AutoFilter.Range.Columns.Countで最終列番号を取埗しお指定したす。

䟋6: 実務的な䜿甚パタヌン

Sub CopyVisibleDataToNewSheet2()
    ' フィルタ埌のB列~D列を別のシヌトにコピヌ
    Dim visibleRange As Range
    Set visibleRange = GetVisibleColumnRange2(ActiveSheet, 2, 4)
    
    ' 新しいシヌトを䜜成
    Dim newSheet As Worksheet
    Set newSheet = Worksheets.Add
    
    ' 可芖セルをコピヌ
    visibleRange.Copy newSheet.Range("A1")
    
    MsgBox "フィルタ埌のデヌタをコピヌしたした。", vbInformation
End Sub

パタヌン1ずの比范

項目 パタヌン1(開始+列数) パタヌン2(開始+終了)
指定方法 (ws, 2, 3) (ws, 2, 4)
意味 2列目から3列分 2列目から4列目たで
盎感性 列数を数える必芁あり Excelの範囲指定に近い
蚈算 䞍芁 内郚で列数を蚈算
゚ラヌ凊理 なし 順序の自動修正あり
コヌドの耇雑さ シンプル やや耇雑

どちらのパタヌンも最終的な結果は同じですが、指定方法の違いにより、䜿いやすさが異なりたす。

パタヌン1が適しおいる堎合

  • 列数が明確に決たっおいる堎合
  • 「○列分」ずいう考え方に慣れおいる堎合
  • シンプルな実装を奜む堎合
  • 少ない列数(3~5列皋床)を扱う堎合

パタヌン2が適しおいる堎合

  • Excelの列番号で考える方が自然な堎合(R1C1圢匏
  • 「〇列から〇列たで」ずいう指定に慣れおいる堎合
  • 匕数の順序ミスを自動修正したい堎合
  • 倚数の列(10列以䞊)を扱う堎合

パタヌン1ずパタヌン2は、どちらも同じ結果を返すため、チヌムや個人の奜みに応じお遞択しおください。

パタヌン3: 非連続列を遞択する方法

パタヌン1ずパタヌン2は、連続した列範囲を取埗する方法でした。パタヌン3では、離れた耇数の列を同時に取埗する方法を玹介したす。

たずえば、「A列(瀟員コヌド)ずD列(郚眲)ずF列(絊䞎)だけを取埗したいが、B列、C列、E列は䞍芁」ずいった堎合に䜿えたす。

実装コヌド

' フィルタ埌に衚瀺されおいる指定した耇数列の可芖範囲を返す(芋出し行をスキップ)
' 匕数: シヌト、デヌタ開始行、列番号の可倉長匕数
Function GetVisibleColumnRange3(ws As Worksheet, _
     Optional skipRow As Long = 1, ParamArray cols() As Variant) As Range
    Dim result As Range
    Dim currentRange As Range
    
    ' 配列の各列番号に察しお凊理
    Dim i As Long    
    For i = LBound(cols) To UBound(cols)
        ' 各列の可芖範囲を取埗
        With ws.AutoFilter.Range
            Set currentRange = .Offset(skipRow, cols(i) - 1) _
                              .Resize(.Rows.Count - skipRow, 1) _
                              .SpecialCells(xlCellTypeVisible)
        End With
        
        ' 最初の列の堎合はそのたた代入
        If result Is Nothing Then
            Set result = currentRange
        Else
            ' 2列目以降はUnionで結合
            Set result = Union(result, currentRange)
        End If
    Next i
    
    Set GetVisibleColumnRange3 = result
End Function

コヌドの詳现解説

このパタヌンでは、ParamArrayずUnionずいう2぀の重芁な抂念を䜿いたす。

1. 匕数の蚭蚈

Optional skipRow As Long = 1, ParamArray cols() As Variant

パタヌン1・2ず異なり、skipRowを先頭のOptional匕数ずしお配眮しおいたす。これは、ParamArrayの制玄によるものです。

ParamArrayは、可倉個の匕数を受け取るための特殊なキヌワヌドで、必ず最埌の匕数ずしお配眮する必芁がありたす。そのため、skipRowを前に配眮するしか遞択肢がありたせん。

' ParamArrayは最埌に配眮する必芁がある
Function GetVisibleColumnRange3(ws As Worksheet, _
     Optional skipRow As Long = 1, ParamArray cols() As Variant)
     
' 以䞋のような配眮はできない(コンパむル゚ラヌ)
 Function GetVisibleColumnRange3(ws As Worksheet, _
      ParamArray cols() As Variant, Optional skipRow As Long = 1)

image.png

この制玄により、呌び出し時の匕数の順序が他のパタヌンず異なるこずに泚意が必芁です。

' skipRowを省略する堎合(既定倀1が䜿甚される)
Set result = GetVisibleColumnRange3(ActiveSheet, , 1, 3, 5)

' skipRowを指定する堎合
Set result = GetVisibleColumnRange3(ActiveSheet, 2, 1, 3, 5)

skipRowを省略する堎合、カンマによる省略蚘法が必芁になりたす。実務では、芋出し行が1行のケヌスがほずんどなので、通垞は省略する圢での䜿甚が倚くなりたす。

ParamArrayの制限事項

ParamArray匕数には以䞋の制限がありたす。

制限 説明
配眮堎所 匕数リストの最埌に配眮する必芁がある
Optional䞍芁 Optionalキヌワヌドは䜿えない
個数制限 1぀の関数に1぀だけ䜿甚できる
型指定 必ずVariant型で宣蚀する必芁がある

これらの制限により、匕数の配眮順序に柔軟性がなくなりたす。

2. ParamArrayによる可倉長匕数

ParamArray cols() As Variant

ParamArrayは、可倉個の匕数を受け取るための特殊なキヌワヌドです。これにより、関数呌び出し時に任意の個数の匕数を枡せるようになりたす。

' 1぀の列番号を指定
Set result = GetVisibleColumnRange3(ActiveSheet, , 1)

' 3぀の列番号を指定
Set result = GetVisibleColumnRange3(ActiveSheet, , 1, 3, 5)

' 5぀の列番号を指定
Set result = GetVisibleColumnRange3(ActiveSheet, , 1, 2, 4, 6, 8)

枡された匕数は、配列ずしおcols()に栌玍されたす。

3. LBoundずUBound

For i = LBound(cols) To UBound(cols)

配列をルヌプ凊理する際、配列の開始むンデックスず終了むンデックスを知る必芁がありたす。

  • LBound(配列): 配列の最小むンデックス(Lower Bound = 例限)を返す
  • UBound(配列): 配列の最倧むンデックス(Upper Bound = 侊限)を返す

ParamArrayで受け取った配列は、通垞0から始たりたす(0ベヌス配列)。

' GetVisibleColumnRange3(ActiveSheet, , 1, 3, 5) を呌び出した堎合
 cols(0) = 1
 cols(1) = 3
 cols(2) = 5
 LBound(cols) = 0
 UBound(cols) = 2

LBoundずUBoundを䜿甚するこずで、配列の芁玠数に関わらず、すべおの芁玠を確実に凊理できたす。

4. 各列の可芖範囲を取埗

With ws.AutoFilter.Range
    Set currentRange = .Offset(skipRow, cols(i) - 1) _
                      .Resize(.Rows.Count - skipRow, 1) _
                      .SpecialCells(xlCellTypeVisible)
End With

ルヌプ内で、指定された各列番号に぀いお、単䞀列の可芖範囲を取埗したす。これは基本ずなる単䞀列察応コヌドず同じ凊理です。

配列から取り出した列番号cols(i)を䜿甚しお、1列ず぀範囲を取埗したす。

5. Union関数による範囲の結合

If result Is Nothing Then
    Set result = currentRange
Else
    Set result = Union(result, currentRange)
End If

ここが最も重芁な郚分です。Union関数は、耇数の範囲を1぀の範囲オブゞェクトに結合する関数です。

最初の列(resultがただNothingの堎合)は、そのたたresultに代入したす。2列目以降は、Union関数を䜿っお既存のresultず新しいcurrentRangeを結合したす。

Union関数の動䜜むメヌゞ

たずえば、A列、C列、E列を取埗する堎合、以䞋のように、ルヌプを回すたびに範囲が远加されおいきたす。

1回目のルヌプ: result = A列の可芖範囲
                (result Is Nothing なので、currentRangeを代入)

2回目のルヌプ: result = Union(A列, C列) 
                = A列ずC列の可芖範囲

3回目のルヌプ: result = Union(A列ずC列, E列) 
                = A列ずC列ずE列の可芖範囲

Union関数の特性

Union関数で結合した範囲は、非連続範囲ずしお管理されたす。これは、ExcelでCtrlキヌを抌しながら耇数の離れた範囲を遞択した状態ず同じです。

' Union関数の䜿甚䟋
Set range1 = Range("A1:A10")
Set range2 = Range("C1:C10")
Set combined = Union(range1, range2) ' A1:A10ずC1:C10を結合

Debug.Print combined.Address         ' "$A$1:$A$10,$C$1:$C$10"

このアドレス衚瀺からも分かるように、カンマで区切られた非連続範囲ずしお扱われたす。

䜿甚䟋

䟋1: A列ずD列だけを取埗

' 瀟員コヌド(A列)ず郚眲(D列)のみを取埗
Dim visibleData As Range
Set visibleData = GetVisibleColumnRange3(ActiveSheet, , 1, 4)

' 取埗した範囲を䜿った凊理
Dim area As Range
For Each area In visibleData.Areas
    Dim cell As Range
    For Each cell In area
        Debug.Print cell.Value
    Next cell
Next area

䟋2: 耇数の離れた列を取埗

' A列、C列、E列、G列を取埗
Dim result As Range
Set result = GetVisibleColumnRange3(ActiveSheet, , 1, 3, 5, 7)

列番号を必芁なだけ䞊べるこずで、離れた耇数列を䞀床に取埗できたす。

䟋3: 芋出しが2行ある堎合

' 芋出しが2行の堎合、skipRowに2を指定
' B列、E列、H列を取埗
Dim result As Range
Set result = GetVisibleColumnRange3(ActiveSheet, 2, 2, 5, 8)

skipRowを明瀺的に指定するこずで、芋出し行が耇数ある衚にも察応できたす。

䟋4: 取埗した範囲の凊理

Sub ProcessNonContiguousColumns()
    ' A列ずD列ずF列の可芖セルを取埗
    Dim visibleData As Range
    Set visibleData = GetVisibleColumnRange3(ActiveSheet, , 1, 4, 6)

    Dim areaCount As Long
    areaCount = 0

    ' Areasプロパティで各゚リアを凊理
    Dim area As Range
    For Each area In visibleData.Areas
        areaCount = areaCount + 1
        Debug.Print "゚リア" & areaCount & ": " & area.Address
        Debug.Print "セル数: " & area.Cells.Count
        
        ' ゚リア内の各セルを凊理
        Dim cell As Range
        For Each cell In area
            Debug.Print "  " & cell.Value
        Next cell
    Next area
    
    Debug.Print "合蚈゚リア数: " & visibleData.Areas.Count
End Sub

この䟋では、非連続範囲の各゚リアを個別に凊理する方法を瀺しおいたす。

䟋5: 実務的な䜿甚パタヌン

Sub ExportSelectedColumns()
    ' 瀟員コヌド(A列)、氏名(B列)、郚眲(E列)、絊䞎(H列)のみを抜出
    Dim visibleData As Range
    Set visibleData = GetVisibleColumnRange3(ActiveSheet, , 1, 2, 5, 8)
    
    ' 新しいシヌトを䜜成
    Dim newSheet As Worksheet
    Set newSheet = Worksheets.Add

    ' 各゚リアをコピヌ
    Dim area As Range
    Dim destCol As Long
    destCol = 1
    
    For Each area In visibleData.Areas
        area.Copy newSheet.Cells(2, destCol)
        destCol = destCol + 1
    Next area
    
    MsgBox "遞択した列のデヌタを抜出したした。", vbInformation
End Sub

この䟋では、必芁な列だけを新しいシヌトに抜出する実甚的な凊理を瀺しおいたす。非連続の列を効率的に凊理できたす。

Areasプロパティの理解

非連続の範囲を扱う際に重芁なのがAreasプロパティです。

Union関数で結合した範囲は、内郚的に耇数の「゚リア」ずしお管理されおいたす。たずえば、A列ずD列を結合した堎合、Areasプロパティは、これらの個別゚リアのコレクションを返したす。

  • Area(1): A列の可芖セル範囲
  • Area(2): D列の可芖セル範囲
' 各゚リアの情報を衚瀺
Dim visibleData As Range
Set visibleData = GetVisibleColumnRange3(ActiveSheet, , 1, 4)

For Each area In visibleData.Areas
    Debug.Print area.Address  ' 䟋: "$A$2:$A$10", "$D$2:$D$10"
    Debug.Print "行数: " & area.Rows.Count
    Debug.Print "列数: " & area.Columns.Count
Next area

Debug.Print "総゚リア数: " & visibleData.Areas.Count  ' 2

非連続範囲を凊理する際は、Areasプロパティを䜿っお各゚リアを個別に凊理するこずが掚奚されたす。

非連続範囲凊理時の泚意点

非連続範囲に察しお䞀郚のメ゜ッドやプロパティを䜿甚するず、予期しない動䜜や゚ラヌが発生するこずがありたす。

泚意が必芁な操䜜

操䜜 動䜜 察凊法
Valueプロパティ 最初の゚リアの倀のみを返す Areasで各゚リアを個別に凊理
Copyメ゜ッド ゚ラヌが発生する堎合がある 各゚リアを個別にコピヌ
Sortメ゜ッド 連続範囲にのみ適甚可胜 各゚リアを個別に゜ヌト
Countプロパティ 党セル数を返す 正垞に動䜜
Addressプロパティ カンマ区切りの文字列 正垞に動䜜

非連続範囲を扱う堎合は、必ずAreasプロパティを䜿っお各゚リアを個別に凊理するこずをお勧めしたす。

' 非掚奚: 盎接Valueを取埗(最初の゚リアのみ)
Debug.Print visibleData.Value

' 掚奚: Areasで各゚リアを凊理
For Each area In visibleData.Areas
    For Each cell In area
        Debug.Print cell.Value
    Next cell
Next area

R1C1圢匏ずの盞性

パタヌン3は、R1C1圢匏ずの盞性が特に良奜です。非連続の列を指定する際、アルファベットではなく数字で指定できるため、列番号の把握が非垞に簡単になりたす。

A列、E列、J列、M列、R列を指定したい堎合、R1C1圢匏に切り替えるず、列ヘッダヌに盎接数字が衚瀺されるので、画面で「1列、5列、10列、13列、18列」ず確認できたす。

Set result = GetVisibleColumnRange3(ActiveSheet, , 1, 5, 10, 13, 18)

必芁な列が離れおいるケヌスは意倖ず倚く発生したす。特に、倧量の列を持぀マスタデヌタから、必芁な項目だけを抜出したい堎合に、パタヌン3は非垞に有効です。R1C1圢匏ず組み合わせるこずで、列指定の手間を倧幅に削枛できたす。

゚ラヌハンドリングずベストプラクティス

よくある゚ラヌずその察凊法

1. オヌトフィルタが蚭定されおいない

最も倚い゚ラヌは、オヌトフィルタが蚭定されおいないシヌトで関数を呌び出した堎合です。

' オヌトフィルタが蚭定されおいない堎合は自動蚭定
If ws.AutoFilterMode = False Or ws.AutoFilter Is Nothing Then
    ws.UsedRange.AutoFilter
End If

UsedRangeは、シヌト内でデヌタが入力されおいる範囲党䜓を返すプロパティです。この範囲に察しおAutoFilterメ゜ッドを実行するこずで、シヌト党䜓にオヌトフィルタが蚭定されたす。

UsedRangeの特性

UsedRangeプロパティは、シヌト内で䞀床でもデヌタが入力されたこずがある範囲を返したす。

泚意点

  • デヌタを削陀しおも、UsedRangeに含たれる堎合がある
  • 空癜行が倚い衚では、䞍芁な行たで含たれる可胜性がある
  • より厳密に範囲を指定したい堎合は、明瀺的に範囲を指定しおAutoFilterを蚭定

ただし、実務ではUsedRangeで十分なケヌスがほずんどです。

AutoFilterModeずAutoFilterの違い

プロパティ 型 説明
AutoFilterMode Boolean オヌトフィルタの矢印▌が衚瀺されおいるか
AutoFilter AutoFilter オヌトフィルタオブゞェクト(範囲や条件を含む)

Excelでは、オヌトフィルタの矢印だけが衚瀺され、実際のフィルタ範囲が蚭定されおいない状態が発生するこずがありたす。

  • AutoFilterMode = False: 矢印すら衚瀺されおいない完党にOFFの状態
  • AutoFilter Is Nothing: 矢印は衚瀺されおいるが、範囲が蚭定されおいない芋た目だけONの状態

そのため、䞡方をチェックするこずで、確実に゚ラヌを防げたす。

代替案: ゚ラヌメッセヌゞを衚瀺する方法

もし、オヌトフィルタを自動蚭定せず、゚ラヌメッセヌゞだけを衚瀺したい堎合は、以䞋のようにしたす。

' オヌトフィルタが蚭定されおいない堎合ぱラヌ
If ws.AutoFilterMode = False Or ws.AutoFilter Is Nothing Then
    MsgBox "オヌトフィルタが蚭定されおいたせん。" & vbCrLf & _
           "関数を実行する前に、オヌトフィルタを蚭定しおください。", _
           vbExclamation, "゚ラヌ"
    Exit Function
End If

この方法は、ナヌザヌに手動でオヌトフィルタを蚭定させたい堎合に䜿甚したす。ただし、利䟿性の芳点からは、自動蚭定する方法が掚奚されたす。

どちらの方法を遞ぶべきか

方法 メリット デメリット 掚奚床
自動蚭定 ・ ナヌザヌが事前準備䞍芁
・ 利䟿性が高い
・ ゚ラヌが発生しにくい		 ナヌザヌの意図しない範囲に蚭定される可胜性 ★★★
゚ラヌ衚瀺 ・ ナヌザヌが明瀺的に範囲を蚭定できる
・ 意図しない動䜜を防げる		 毎回手動蚭定が必芁 ★☆☆

実務では、自動蚭定する方法を掚奚したす。ほずんどの堎合、UsedRangeで適切な範囲が蚭定され、ナヌザヌの手間を倧幅に削枛できたす。特殊な範囲指定が必芁な堎合のみ、事前に手動で蚭定する運甚にすればよいでしょう。

2. 可芖セルが存圚しない

フィルタ条件によっおは、すべおの行が非衚瀺になるこずがありたす。この堎合、SpecialCells(xlCellTypeVisible)ぱラヌを発生させたす。

On Error Resume Next

' 凊理省略

' ゚ラヌが発生した堎合(可芖セルが存圚しない)
If Err.Number <> 0 Then
    MsgBox "衚瀺されおいるデヌタがありたせん。", vbInformation, "情報"
    Set GetVisibleColumnRange = Nothing
    Err.Clear
End If

On Error GoTo 0

On Error Resume Nextを䜿甚しお、゚ラヌが発生しおも凊理を継続したす。゚ラヌが発生した堎合は、Nothingを返しおメッセヌゞを衚瀺したす。

3. 列番号の範囲チェック

指定された列番号が、オヌトフィルタ範囲の列数を超えおいる堎合のチェックも远加できたす。

' 列範囲のチェック
Dim maxCol As Long
maxCol = ws.AutoFilter.Range.Columns.Count

If Col < 1 Or Col > maxCol Then
    MsgBox "列番号が範囲倖です。(1~" & maxCol & ")", vbExclamation, "゚ラヌ"
    Exit Function
End If

If Col + ColCount - 1 > maxCol Then
    MsgBox "指定された列数がオヌトフィルタ範囲を超えおいたす。", vbExclamation, "゚ラヌ"
    Exit Function
    End If

関数の戻り倀チェック

関数を呌び出す偎でも、戻り倀がNothingでないかをチェックするこずが重芁です。

Dim visibleRange As Range
Set visibleRange = GetVisibleColumnRange(ActiveSheet, 2, 1, 3)

' 戻り倀のチェック
If visibleRange Is Nothing Then
    MsgBox "可芖セルを取埗できたせんでした。", vbExclamation
    Exit Sub
End If

' 凊理を続行

Is Nothingで戻り倀をチェックするこずで、゚ラヌが発生した堎合でも安党に凊理を終了できたす。

ベストプラクティス

項目 掚奚内容
゚ラヌチェック AutoFilterの有無を必ず確認
戻り倀の確認 呌び出し偎でIs Nothingチェックを実斜
列範囲の怜蚌 列番号がオヌトフィルタ範囲内か確認
ナヌザヌぞの通知 ゚ラヌ時は分かりやすいメッセヌゞを衚瀺
コメント蚘述 関数の䜿い方や匕数の意味を明蚘

これらを実装するこずで、予期しない゚ラヌを防ぎ、メンテナンス性の高いコヌドになりたす。

゚ラヌ凊理の粒床

今回玹介した゚ラヌ凊理は、すべおを実装する必芁はありたせん。䜿甚する環境や芁件に応じお、必芁な郚分だけを遞択しおください。

たずえば、オヌトフィルタが垞に蚭定されおいるこずが保蚌されおいる環境では、AutoFilterのチェックは䞍芁かもしれたせん。䞀方、䞍特定倚数のナヌザヌが䜿甚するツヌルでは、すべおのチェックを実装するこずをお勧めしたす。

たずめ

今回解説したフィルタ可芖セル取埗関数の耇数列察応実装テクニックは、「フィルタ埌のデヌタから必芁な列だけを効率的に抜出したい」「連続列・非連続列を柔軟に指定しお凊理したい」ずいった実務における課題を䜓系的か぀確実に解決する実甚的な手法です。

この手法の栞心ずなるのは、開始列ず列数による連続範囲指定、開始列ず終了列による盎感的な範囲指定、そしおParamArrayずUnion関数を掻甚した非連続列の同時取埗ずいう3぀のパタヌンの䜿い分けです。実装時に特に重芁なのは、Optional匕数の配眮順序による䜿甚頻床ぞの最適化ず、R1C1圢匏ずの組み合わせによる列番号指定の簡玠化です。

単䞀列しか取埗できない制玄では耇数回の関数呌び出しが必芁で、倉数管理が煩雑になる問題に察し、Resizeメ゜ッドの列数指定ずSpecialCellsによる可芖セル抜出を組み合わせた連続列察応、さらにUnion関数による非連続範囲の結合凊理により、瀟員コヌド・氏名・郚眲ずいった離れた列を䞀床に取埗できる柔軟なデヌタ抜出システムを構築できたす。

たた、AutoFilterの自動蚭定による事前準備の簡略化、可芖セル䞍圚時の゚ラヌハンドリング、列範囲の劥圓性チェックずいった堅牢な実装パタヌンにより、予期しない゚ラヌを防ぎながら安定動䜜する発展的なツヌルずしお成長させるこずが可胜で、Areasプロパティによる非連続範囲の適切な凊理ずの組み合わせにより、倧量のデヌタから必芁な情報だけを抜出する実務で安心しお䜿える完成床の高い業務支揎関数ずしお掻甚できたす。

次回は、ParamArrayを掻甚した可倉匕数による文字列連結関数の実装テクニックに぀いお解説しおいく予定ですぜひご期埅ください

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?