Excel VBAで可視セルのみを効率的にコピーするテクニック
私はVBAの活用経験を通じて得た知識を整理し、共有する目的で記事を作成しているプログラミング歴1年半になるエンジニアです。前回は、可視セルを活用したフィルター操作について詳しく説明しました。今回は、フィルターが適用された状態で表示されているセル（可視セル）のみを、行と列の非表示状態を保持したまま別の場所にコピーする実用的なテクニックについて解説します。
- 第1回: Excel VBAの基礎知識とセキュリティ設定
- 第2回: Excel VBAの基本操作とオブジェクトの理解
- 第3回: Excel VBAにおける変数と定数の基本
- 第4回: Excel VBAにおけるシート操作の基本とエラー処理
- 第5回: Excel VBAにおける条件分岐
- 第6回: Excel VBAにおける繰り返し処理の基本
- 第7回: Excel VBAにおける配列とFor Eachの活用
- 第8回: Excel VBAにおけるFormulaとValueの使い分けとユーザー入力の取得
- 第9回: Excel VBAにおけるファイル操作とフォルダ管理の基本
- 第10回: Excel VBAにおけるFileSystemObjectを活用した高度なファイル操作
- 第11回: Excel VBAにおけるFileSystemObjectを活用した高度なファイル操作 応用編
- 第12回: Excel VBAにおけるStrConv関数の活用と応用テクニック
- 第13回: Excel VBAにおけるワークブックの安全な操作と管理テクニック
- 第14回: Excel VBAにおけるFunction（関数）の作成と活用テクニック
- 第15回: Excel VBAにおける配列を返す関数の作成と活用テクニック
- 第16回: Excel VBAにおけるコレクションの活用と応用テクニック
- 第17回: Excel VBAにおける辞書型（Dictionary）の活用と応用テクニック
- 第18回: Excel VBAにおけるEnum型を活用した関数設計と実装テクニック
- 第19回: Excel VBAにおけるユーティリティ関数の作成と活用テクニック
- 第20回: Excel VBAにおける正規表現を活用したファイル名解析テクニック
- 第21回: Excel VBAで範囲内の図形を効率的に削除するテクニック
- 第22回: Excel VBAで最新ファイルを効率的に検索する関数設計テクニック
- 第23回: Excel VBAで選択した範囲に対して、一行おきに空行を挿入するテクニック
- 第24回: Excel VBAで可視セルを活用したフィルター操作テクニック
- 第25回: Excel VBAで可視セルのみを効率的にコピーするテクニック（本記事）
目次
はじめに
Excelでデータを扱う際、フィルター機能や行・列の非表示機能を使用することで、必要なデータのみを表示させることがよくあります。この状態でExcelの標準コピー機能を使用すると、表示されているセルのみがコピーされます。これは非常に便利な挙動です。
しかし、この「可視セルのみ」でコピーしたデータを、同じく行や列が非表示になっている別のシートや範囲に貼り付ける場合、貼り付け先で非表示になっているセルにもデータが貼り付けられてしまいます。これにより、既存の非表示セルのデータが上書きされ、意図した通りの結果にならないことがあります。
そのため、可視化セルのみコピーしたデータを行や列が非表示になっているシートに貼り付けることができる機能は非常に重要です。手動でセルを一つずつコピーしていては、データ量が多い場合に現実的ではありません。
今回紹介するマクロは、このようにコピー元で選択した「表示されているセル」を、貼り付け先のシートでも非表示になっている行・列をスキップし、表示されているセルにのみ順番に貼り付けることを目的としています。これにより、貼り付け先の非表示設定を維持したまま、意図した通りのデータ配置を実現し、データの整理作業を大幅に効率化することができます。
実装における主要な技術要素
今回の実装では、以下の技術要素を組み合わせます。
- Collectionオブジェクト: 可視行・列のインデックスを動的に管理
- 配列処理: 大量データの高速読み取り
- Hidden プロパティ: 行・列の表示状態の判定
- InputBox: ユーザーとのインタラクション
コレクションを活用した可視行・列の特定
可視行の特定処理
可視セルコピーの核心は、どの行と列が表示されているかを正確に把握することです。以下のコードで可視行を特定します。
' コピー元の可視行を特定するためのコレクション
Dim sourceVisibleRows As New Collection
' ループ用の変数
Dim i As Long
' コピー元の各行が可視かどうかをチェック
For i = 1 To maxRows
' 行が非表示でなければコレクションに追加
If Not sourceRange.Rows(i).Hidden Then
sourceVisibleRows.Add i
End If
Next i
Hidden プロパティの活用
If Not sourceRange.Rows(i).Hidden Then
Hiddenプロパティは、行や列が非表示状態かどうかを示すBoolean値です。Not演算子を使用することで、「非表示でない（つまり表示されている）」行のみを選別します。
可視列の特定処理
行と同様に、表示されている列も特定します。
' コピー元の可視列を特定するためのコレクション
Dim sourceVisibleCols As New Collection
' コピー元の各列が可視かどうかをチェック
For j = 1 To maxCols
' 列が非表示でなければコレクションに追加
If Not sourceRange.Columns(j).Hidden Then
sourceVisibleCols.Add j
End If
Next j
コピー先の可視セル特定
コピー先でも同様に、表示されている行・列を特定する必要があります。これにより、コピー先の非表示状態を保持できます。
' コピー先の可視行を特定するためのコレクション
Dim destVisibleRows As New Collection
' コピー先の開始行
Dim currentRow As Long: currentRow = destStartRow
' 必要な可視行の数（コピー元の可視行と同じ）
Dim rowsNeeded As Long: rowsNeeded = sourceVisibleRows.Count
' 見つけた可視行の数
Dim rowsFound As Long: rowsFound = 0
' 必要な数の可視行が見つかるまでループ
Do While rowsFound < rowsNeeded
' 行が非表示でなければコレクションに追加
If Not destWs.Rows(currentRow).Hidden Then
destVisibleRows.Add currentRow
rowsFound = rowsFound + 1
End If
' 次の行へ
currentRow = currentRow + 1
Loop
完全版の実装と詳細解説
以下は、エラーハンドリングとパフォーマンス最適化を含む完全版の実装です。
' 可視セルを値のみ転記
Sub CopyVisibleCellsOnly()
' エラー処理用の変数
Dim errNum As Long, errDesc As String
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' 選択された範囲をコピー元として設定
Dim sourceRange As Range
Set sourceRange = Selection
' コピー元のワークシートを変数に格納
Dim sourceWs As Worksheet
Set sourceWs = sourceRange.Worksheet
' ダイアログボックスを表示してコピー先セルを選択してもらう
Dim destCell As Range
Set destCell = Application.InputBox("コピー先の先頭セルを選択してください。", _
"コピー先", Type:=8)
' キャンセルされた場合は処理を終了
If destCell Is Nothing Then
GoTo Cleanup
End If
' コピー先のワークシートと開始位置を設定
Dim destWs As Worksheet
Set destWs = destCell.Worksheet
' コピー先の開始行番号を取得
Dim destStartRow As Long
destStartRow = destCell.Row
' コピー先の開始列番号を取得
Dim destStartCol As Long
destStartCol = destCell.Column
' コピー元のデータを一度に配列として取得（高速化のため）
Dim dataArray As Variant
dataArray = sourceRange.Value
' データの行数を取得
Dim maxRows As Long
maxRows = UBound(dataArray, 1)
' データの列数を取得
Dim maxCols As Long
maxCols = UBound(dataArray, 2)
' コピー元の可視行を特定してコレクションに格納
Dim sourceVisibleRows As New Collection
Dim i As Long
For i = 1 To maxRows
' 行が非表示でなければコレクションに追加
If Not sourceRange.Rows(i).Hidden Then
sourceVisibleRows.Add i
End If
Next i
' コピー元の可視列を特定してコレクションに格納
Dim sourceVisibleCols As New Collection
Dim j As Long
For j = 1 To maxCols
' 列が非表示でなければコレクションに追加
If Not sourceRange.Columns(j).Hidden Then
sourceVisibleCols.Add j
End If
Next j
' コピー先の可視行を特定してコレクションに格納
Dim destVisibleRows As New Collection
Dim currentRow As Long
currentRow = destStartRow
' 必要な可視行数
Dim rowsNeeded As Long
rowsNeeded = sourceVisibleRows.Count
' 見つけた可視行数
Dim rowsFound As Long
rowsFound = 0
' 必要な可視行数が見つかるまでループ
Do While rowsFound < rowsNeeded
' 行が非表示でなければコレクションに追加
If Not destWs.Rows(currentRow).Hidden Then
destVisibleRows.Add currentRow
rowsFound = rowsFound + 1
End If
currentRow = currentRow + 1
Loop
' コピー先の可視列を特定してコレクションに格納
Dim destVisibleCols As New Collection
Dim colOffset As Long
colOffset = 0
' 可視列分だけループ
For j = 1 To sourceVisibleCols.Count
' 対象の列位置を計算
Dim targetCol As Long
targetCol = destStartCol + colOffset
' 非表示列をスキップ
Do While destWs.Columns(targetCol).Hidden
targetCol = targetCol + 1
colOffset = colOffset + 1
Loop
' 可視列をコレクションに追加
destVisibleCols.Add targetCol
colOffset = colOffset + 1
Next j
' 実際のデータコピー処理
For i = 1 To sourceVisibleRows.Count
' コピー元の行インデックスを取得
Dim sourceVisibleRowIndex As Long
sourceVisibleRowIndex = sourceVisibleRows(i)
' コピー先の行インデックスを取得
Dim destVisibleRowIndex As Long
destVisibleRowIndex = destVisibleRows(i)
' 列方向のコピー処理
For j = 1 To sourceVisibleCols.Count
' コピー元の列インデックスを取得
Dim sourceCol As Long
sourceCol = sourceVisibleCols(j)
' コピー先の列インデックスを取得
Dim destCol As Long
destCol = destVisibleCols(j)
' データを転記
destWs.Cells(destVisibleRowIndex, destCol).Value = _
dataArray(sourceVisibleRowIndex, sourceCol)
Next j
Next i
' コピー完了のメッセージを表示
MsgBox sourceVisibleRows.Count & "行 × " _
& sourceVisibleCols.Count & "列 の表示データをコピーしました。" _
& vbCrLf & "貼り付け先でご確認ください。", vbInformation
Cleanup:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
' オブジェクトを解放してメモリを効率化
Set sourceRange = Nothing
Set destCell = Nothing
Set sourceWs = Nothing
Set destWs = Nothing
Set sourceVisibleRows = Nothing
Set sourceVisibleCols = Nothing
Set destVisibleRows = Nothing
Set destVisibleCols = Nothing
Exit Sub
ErrorHandler:
' エラー情報を取得
errNum = Err.Number
errDesc = Err.Description
' エラーの種類に応じたメッセージを表示
If errNum = 424 Then
' InputBoxのキャンセルエラー
MsgBox "コピーがキャンセルされました。", vbExclamation
Else
' その他のエラー
MsgBox "エラーが発生しました: " & errNum & " - " & errDesc, vbCritical
End If
' クリーンアップ処理へ移動
Resume Cleanup
End Sub
エラーハンドリングとパフォーマンス最適化のポイント
VBAで安全で効率的なプログラムを作成するためには、エラーハンドリングとパフォーマンスの両方に注意を払う必要があります。今回のマクロで使用している主要なテクニックについて解説します。
InputBoxのキャンセル処理
ユーザーがダイアログでキャンセルボタンを押した場合の処理は、特別な配慮が必要です。
' ダイアログボックスを表示してコピー先セルを選択してもらう
Dim destCell As Range
Set destCell = Application.InputBox("コピー先の先頭セルを選択してください。", _
"コピー先", Type:=8)
' キャンセルされた場合は処理を終了
If destCell Is Nothing Then
GoTo Cleanup ' リソース解放処理へ移動
End If
Type:=8を指定することで、ユーザーはセル範囲のみを選択できるようになります。キャンセル時はNothingが返されるため、早期リターンで処理を終了します。
InputBox の Type パラメータ
- Type:=1: 数値のみ入力可能
- Type:=2: 文字列のみ入力可能
- Type:=8: セル参照（Range オブジェクト）のみ選択可能
- Type:=64: 配列データの入力
InputBoxについて、さらに詳しく知りたい方は、私が書いた記事
「第8回: Excel VBAにおけるFormulaとValueの使い分けとユーザー入力の取得」もぜひご覧ください。
効率的なデータ読み取り
大量データを扱う際は、セルに一つずつアクセスするのではなく、一括で配列に読み込むことが重要です。
' コピー元のデータを一度に配列として取得（高速化のため）
Dim dataArray As Variant
dataArray = sourceRange.Value ' 範囲全体を一括で配列に格納
' 後でこの配列からデータを取得して使用
destWs.Cells(destVisibleRowIndex, destCol).Value = _
dataArray(sourceVisibleRowIndex, sourceCol)
この方法により、個別のセルアクセスと比較して処理速度が大幅に向上します。ただし、メモリ使用量は増加するため、極端に大きなデータでは注意が必要です。
統一されたエラーハンドリング構造
エラーが発生した場合とそうでない場合の両方で、適切にリソースを解放するための構造を作成します。
Sub CopyVisibleCellsOnly()
' エラー処理の設定
On Error GoTo ErrorHandler
' 画面更新と計算を一時的に停止（高速化のため）
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' ここにメインの処理を記述
' ...
' 正常終了時のメッセージ表示
MsgBox "処理が完了しました。", vbInformation
Cleanup:
' 画面更新と計算を再開
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
' オブジェクトを解放してメモリを効率化
Set sourceRange = Nothing
Set destCell = Nothing
' その他のオブジェクト変数もすべて Nothing に設定
Exit Sub
ErrorHandler:
' エラーの種類に応じたメッセージを表示
If Err.Number = 424 Then
MsgBox "処理がキャンセルされました。", vbExclamation
Else
MsgBox "エラーが発生しました: " & Err.Number & " - " & Err.Description, vbCritical
End If
' クリーンアップ処理へ移動
Resume Cleanup
End Sub
Collectionオブジェクトの活用メリット
配列の代わりにCollectionを使用することで、より柔軟なデータ管理が可能になります。
' 可視行を格納するコレクションを作成
Dim sourceVisibleRows As New Collection
' 行が表示されている場合のみコレクションに追加
For i = 1 To maxRows
If Not sourceRange.Rows(i).Hidden Then ' 行が非表示でない場合
sourceVisibleRows.Add i ' 行番号をコレクションに追加
End If
Next i
' コレクションに格納された要素数を取得
Dim visibleRowCount As Long
visibleRowCount = sourceVisibleRows.Count
Collectionと配列の比較
| 特徴 | 配列 | Collection |
|---|---|---|
| サイズ指定 | 事前に必要 | 動的に拡張可能 |
| 要素追加 | 複雑（ReDim必要） | Add メソッドで簡単 |
| メモリ効率 | 高い | やや低い |
| 用途 | サイズが決まっている場合 | サイズが不明な場合 |
パフォーマンス最適化の考慮点
処理速度を向上させるための設定変更も重要です。
' 処理開始前に画面更新を停止
Application.ScreenUpdating = False ' 画面の更新を停止
Application.Calculation = xlCalculationManual ' 自動計算を停止
' 処理完了後に設定を元に戻す
Application.ScreenUpdating = True ' 画面の更新を再開
Application.Calculation = xlCalculationAutomatic ' 自動計算を再開
これらの設定により、処理中の画面のちらつきを防ぎ、不要な再計算を避けることで、大幅な処理速度の向上が期待できます。
このような統一されたエラーハンドリング構造を採用することで、エラーが発生した場合でも安全にプログラムを終了でき、Excelの動作が不安定になることを防げます。
書式付きコピーバージョンの実装
値のみをコピーする方法に加えて、セルの書式（フォント、背景色、罫線など）も含めてコピーしたい場面があります。ここでは、見た目の情報も含めて可視セルをコピーする方法について解説します。
値のみコピーと書式付きコピーの違い
これまでのコードでは、セルに格納されているデータの値だけを別のセルに転送していました。これは、配列に読み込んだ値をそのまま別のセルのValueプロパティに代入する方法です。
' 値のみをコピーする方法
destWs.Cells(destVisibleRowIndex, destCol).Value = dataArray(sourceVisibleRowIndex, sourceCol)
この方法では、数値や文字列などのデータは正確にコピーされますが、セルの見た目に関する情報（書式）は転送されません。
一方、書式付きコピーでは、データと一緒に以下の書式情報も転送します。
- フォント（種類、サイズ、色、太字など）
- 背景色（セルの塗りつぶし色）
- 罫線（線の種類、太さ、色）
- 数値の表示形式（桁区切り、小数点以下の桁数など）
- 文字の配置（左寄せ、中央寄せ、右寄せなど）
Copyメソッドを使用した書式付きコピー
書式情報も含めてセルをコピーするには、Copyメソッドを使用します。このメソッドは、Excelの標準的なコピー機能と同じように、セルの全ての情報を転送できます。
' 書式付きでセルをコピーする方法
sourceRange.Cells(sourceVisibleRowIndex, sourceCol).Copy _
destWs.Cells(destVisibleRowIndex, destCol)
この一行のコードで、コピー元のセルに設定されている全ての書式情報が、コピー先のセルに適用されます。
書式付きコピーのコード実装
' 可視セルを書式付きでコピーする
Sub CopyVisibleCellsWithFormat()
Dim errNum As Long, errDesc As String
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' 選択された範囲をコピー元として設定
Dim sourceRange As Range
Set sourceRange = Selection
' コピー元のワークシートを変数に格納
Dim sourceWs As Worksheet
Set sourceWs = sourceRange.Worksheet
' ダイアログボックスを表示してコピー先セルを選択してもらう
Dim destCell As Range
Set destCell = Application.InputBox("コピー先の先頭セルを選択してください。", _
"コピー先", Type:=8)
' キャンセルされた場合は処理を終了
If destCell Is Nothing Then
GoTo Cleanup
End If
' コピー先のワークシートを設定
Dim destWs As Worksheet
Set destWs = destCell.Worksheet
' コピー先の開始行番号を取得
Dim destStartRow As Long
destStartRow = destCell.Row
' コピー先の開始列番号を取得
Dim destStartCol As Long
destStartCol = destCell.Column
' -----------------------------------------------------------
' 【変更箇所1】配列を使用せず、直接範囲の行数・列数を取得
' -----------------------------------------------------------
' コピー元の行数を取得
Dim maxRows As Long
maxRows = sourceRange.Rows.Count ' ← 変更: .Value配列ではなく直接取得
' コピー元の列数を取得
Dim maxCols As Long
maxCols = sourceRange.Columns.Count ' ← 変更: .Value配列ではなく直接取得
' コピー元の可視行の相対インデックスを格納するコレクション
Dim sourceVisibleRows As New Collection
' コピー元の各行をループして可視行を特定
Dim i As Long
For i = 1 To maxRows
' 行が非表示でなければコレクションに追加
If Not sourceRange.Rows(i).Hidden Then
sourceVisibleRows.Add i
End If
Next i
' コピー元の可視列の相対インデックスを格納するコレクション
Dim sourceVisibleCols As New Collection
' コピー元の各列をループして可視列を特定
Dim j As Long
For j = 1 To maxCols
' 列が非表示でなければコレクションに追加
If Not sourceRange.Columns(j).Hidden Then
sourceVisibleCols.Add j
End If
Next j
' コピー先の可視行を格納するコレクション
Dim destVisibleRows As New Collection
' コピー先の現在行を格納する変数
Dim currentRow As Long
currentRow = destStartRow
' 必要な可視行の数を取得
Dim rowsNeeded As Long
rowsNeeded = sourceVisibleRows.Count
' 見つけた可視行の数を格納する変数
Dim rowsFound As Long
rowsFound = 0
' 必要な数の可視行が見つかるまでループ
Do While rowsFound < rowsNeeded
' 行が非表示でなければコレクションに追加
If Not destWs.Rows(currentRow).Hidden Then
destVisibleRows.Add currentRow
rowsFound = rowsFound + 1
End If
' 次の行へ移動
currentRow = currentRow + 1
Loop
' コピー先の可視列を格納するコレクション
Dim destVisibleCols As New Collection
' 列の位置調整用のオフセットを初期化
Dim colOffset As Long
colOffset = 0
' 可視列分だけループ
For j = 1 To sourceVisibleCols.Count
' 対象の列位置を計算
Dim targetCol As Long
targetCol = destStartCol + colOffset
' 非表示列をスキップ
Do While destWs.Columns(targetCol).Hidden
targetCol = targetCol + 1
colOffset = colOffset + 1
Loop
' 可視列をコレクションに追加
destVisibleCols.Add targetCol
colOffset = colOffset + 1
Next j
' -----------------------------------------------------------
' 【変更箇所2】メインのコピー処理 - 書式付きコピーに変更
' -----------------------------------------------------------
' 実際のコピー処理：各可視セルを書式付きでコピー
For i = 1 To sourceVisibleRows.Count
' コピー元の行インデックスを取得
Dim sourceVisibleRowIndex As Long
sourceVisibleRowIndex = sourceVisibleRows(i)
' コピー先の行インデックスを取得
Dim destVisibleRowIndex As Long
destVisibleRowIndex = destVisibleRows(i)
' 列方向のコピー処理
For j = 1 To sourceVisibleCols.Count
' コピー元の列インデックスを取得
Dim sourceCol As Long
sourceCol = sourceVisibleCols(j)
' コピー先の列インデックスを取得
Dim destCol As Long
destCol = destVisibleCols(j)
' 変更: 書式付きでセルをコピー（値・書式・数式すべて含む）
sourceRange.Cells(sourceVisibleRowIndex, sourceCol).Copy _
destWs.Cells(destVisibleRowIndex, destCol)
' 従来: destWs.Cells(destVisibleRowIndex, destCol).Value = _
' dataArray(sourceVisibleRowIndex, sourceCol)
Next j
Next i
' -----------------------------------------------------------
' 【変更箇所3】完了メッセージの変更
' -----------------------------------------------------------
MsgBox sourceVisibleRows.Count & "行 × " _
& sourceVisibleCols.Count & "列 の表示データを書式付きでコピーしました。" _
& vbCrLf & "貼り付け先でご確認ください。", vbInformation
Cleanup:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Set sourceRange = Nothing
Set destCell = Nothing
Set sourceWs = Nothing
Set destWs = Nothing
Set sourceVisibleRows = Nothing
Set sourceVisibleCols = Nothing
Set destVisibleRows = Nothing
Set destVisibleCols = Nothing
Exit Sub
ErrorHandler:
errNum = Err.Number
errDesc = Err.Description
If errNum = 424 Then
MsgBox "コピーがキャンセルされました。", vbExclamation
Else
MsgBox "エラーが発生しました: " & errNum & " - " & errDesc, vbCritical
End If
Resume Cleanup
End Sub
変更箇所の説明
書式付きコピー版では、値のみコピー版から以下の3つの重要な変更を行っています。
1. データサイズ取得方法の変更
値のみ版では、処理速度を重視して範囲全体を配列に読み込んでいました。
dataArray = sourceRange.Value ' 範囲を配列として一括取得
maxRows = UBound(dataArray, 1) ' 配列の行数を取得
書式付き版では、配列を使わずに範囲オブジェクトから直接サイズを取得します。
maxRows = sourceRange.Rows.Count ' 範囲の行数を直接取得
maxCols = sourceRange.Columns.Count ' 範囲の列数を直接取得
この変更により、メモリ使用量を抑えつつ、個別のセルにアクセスしやすくなります。
2. コピー処理の根本的な変更
最も重要な変更点は、データを転送する方法です。
値のみ版では、配列から値を取り出して代入していました。
' 配列の値をセルのValueプロパティに代入
destWs.Cells(destVisibleRowIndex, destCol).Value = _
dataArray(sourceVisibleRowIndex, sourceCol)
書式付き版では、Copyメソッドを使用してセル全体をコピーします。
' Copyメソッドでセルの全情報を転送
sourceRange.Cells(sourceVisibleRowIndex, sourceCol).Copy _
destWs.Cells(destVisibleRowIndex, destCol)
3. ユーザーへのメッセージ改善
処理完了時のメッセージも、実際の処理内容に合わせて更新しています。
' 書式付きコピーであることを明示
MsgBox "～の表示データを書式付きでコピーしました。"
これにより、ユーザーは「値だけでなく見た目も含めてコピーされた」ことを明確に理解できます。
変更点概要
' 可視セルを書式付きでコピーする
Sub CopyVisibleCellsWithFormat()
' [初期化処理は共通のため省略...]
' データサイズの取得方法
' ↓ 変更点：配列を使わずに直接取得
Dim maxRows As Long
maxRows = sourceRange.Rows.Count ' ← 書式付き版：直接範囲から取得
' ※値のみ版：dataArray = sourceRange.Value → maxRows = UBound(dataArray, 1)
Dim maxCols As Long
maxCols = sourceRange.Columns.Count ' ← 書式付き版：直接範囲から取得
' ※値のみ版：maxCols = UBound(dataArray, 2)
' [可視行・列の特定処理は両版で共通...]
' メインのコピー処理
For i = 1 To sourceVisibleRows.Count
For j = 1 To sourceVisibleCols.Count
' ↓ 変更点：書式付きコピーに変更
sourceRange.Cells(sourceVisibleRowIndex, sourceCol).Copy _
destWs.Cells(destVisibleRowIndex, destCol)
' ↑ 書式付き版：Copyメソッドで書式も含めて転送
'
' ※値のみ版では以下のコード
' destWs.Cells(destVisibleRowIndex, destCol).Value = _
' dataArray(sourceVisibleRowIndex, sourceCol)
Next j
Next i
' 完了メッセージ
' ↓ 変更点：「書式付き」を追加
MsgBox "～の表示データを書式付きでコピーしました。"
' ↑ 値のみ版では「書式付きで」の文言なし
End Sub
パフォーマンスへの影響
書式付きコピーは、値のみコピーと比較して処理時間が長くなる傾向があります。これは、各セルを個別に処理する必要があるためです。しかし、正確な書式情報の転送が必要な場面では、この処理時間の増加は必要なコストと考えられます。
大量のデータを扱う場合は、処理時間を考慮して適切な方法を選択することが重要です。
まとめ
今回解説した可視セルコピーテクニックは、フィルターや非表示行・列を含むExcelファイル間でのデータ転記作業を劇的に効率化する実用的な手法です。このテクニックを活用することで、表示されているセルのみを正確に別の場所に転記しつつ、コピー先の非表示状態も維持するという、手動では非常に煩雑な作業を完全自動化できます。
この手法の核心となるのは、Collectionオブジェクトによる可視行・列の動的管理、Hidden プロパティを活用した表示状態の正確な判定、そして配列処理による高速データ読み取りです。これらの技術要素を組み合わせることで、コピー元とコピー先の両方で非表示設定を保持したまま、意図した通りのデータ配置を実現する柔軟なシステムを構築できます。
値のみコピーと書式付きコピーの2つのバージョンを提供することで、用途に応じた最適な処理方法を選択でき、InputBoxによるユーザビリティの向上、統一されたエラーハンドリング構造による安全性の確保、Application設定の最適化によるパフォーマンス向上も実現しています。
実際の業務で活用する際には、大量データ処理時のメモリ管理、エラー発生時のリソース解放、ユーザーインターフェースの改良などを考慮することで、より堅牢で使いやすいツールを作成できます。また、このようなセル単位の精密な制御技術をマスターすることで、複雑なデータ整理や集計処理システムの開発基盤も身につけることができます。
次回は、アーカイブフォルダからファイルを手作業で取り出す面倒な作業を完全自動化する再帰処理テクニックをご紹介します。どんなに深い階層構造でもフォルダ構造を維持したまま移動し、同名ファイルの衝突も自動的にリネームで解決する実用的なマクロを解説。「手作業でアーカイブから取り出し→マクロ実行」という2段階作業が、ボタン一つで完結する自動化システムをご紹介します。ぜひご期待ください！