和風スパゲティのレシピ

日本語でコーディングするExcelVBA

トップ > VBAの落とし穴 > セル範囲のコピー&ペーストに非表示のセルは含まれる？

セル範囲のコピー&ペーストに非表示のセルは含まれる？

VBAの落とし穴 Range・Cells VBA

いきなりですが問題です（じゃじゃん）

↓この表を別のシートにコピー＆ペーストします。
非表示コピペの元の表

問題１　フィルター抽出中にコピー＆ペーストすると結果は？

フィルターしてコピー






◇ 正解

フィルター抽出でペースト


抽出していないレコードは、ペーストされません。

問題２　列の非表示中にコピー＆ペーストすると結果は？

列非表示でコピー





◇ 正解

列非表示ペースト


非表示にしている列もペーストされます。





この2つの作業は良くやりますので、
この仕様の違いは、知っている方も多いのではないでしょうか。


一貫性がないと思う方もいらっしゃるかもしれませんが、
個人的には、どちらも便利だし、納得できる仕様です。


フィルターによる非抽出は「データ的な作業」なので、
コピー時についてこないのはうれしい仕様です。


行列の非表示は「人間が見る/見せる的な作業」なので、
コピーでは非表示もついてきてほしい場面の方が多いです。


この2つの機能で、ペーストに含まれるか否かの挙動が変わることに、
個人的には違和感はありません。


問題３　フィルターと列非表示を、両方使ってコピペすると結果は？

両方コピー







◇ 正解　なんとこうなります

両方ペースト

非表示にしただけのB列が、ペーストされなくなっていますね…。


詳しくはわかりませんが、
オートフィルタで絞られていると、
「非表示のセルがコピーについていかない」フラグが立ち、
この状態でのコピペでは、非表示列もコピーに含まれなくなります。


「オートフィルターで絞ってコピペ」は、
書きやすさでも実行速度でもすごく優秀なので、
ガンガン使うべきですが、この仕様には注意しましょう。

解決策

手で作業してるときは頑張って気付づいてください(笑)


VBAでこの処理を行う際は、

' すべての列を表示する
ws指定シート.Cells.EntireColumn.Visible = True

↑この後にコピーペーストの処理を書いてください。


もし列の表示/非表示を変えたくない場合は、
コピー元のデータを、作業完了後に保存せずに閉じてしまうか、

↓こんな風に非表示列を記憶しておき、作業終了後に復元してください。

' コピー元シートの非表示列をコレクションに記憶
Dim 非表示列の列番号リスト As New Collection
Dim C As Long
For C = 1 To コピー元シート.UsedRange.Columns.Count + コピー元シート.UsedRange.Column - 1
    If コピー元シート.Columns(C).Hidden = True Then
        非表示列の列番号リスト.Add (C)
    End If
Next

' すべての列を表示
コピー元シート.Cells.EntireColumn.Hidden = False

' ここにコピーペーストの処理を書く（サンプルはオートフィルターエリアすべてを値だけコピー）
コピー元シート.AutoFilter.Range.Copy
ペースト基準セル.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
        
' 列の非表示を復元
If 非表示列の列番号リスト.Count > 0 Then
    Dim 非表示列の列番号
    For Each 非表示列の列番号 In 非表示列の列番号リスト
        コピー元シート.Columns(非表示列の列番号).Hidden = True
    Next
End If

そんなに複雑な処理ではないですが長たらしいので、
この作業をよく行う方は、「フィルター抽出中のデータをコピーする」のような、
汎用関数にして持っておいた方がいいかもしれませんね。