大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、可視セルの判定、コピーと貼り付けをする方法について、ご紹介します。使うVBA関数は、HiddenとSpecialCellsで、あと、オートフィルタも使います。可視セルをコピーして貼り付けする場合、オートフィルタを使うと簡単になります。

はじめに

この記事では、可視セルを判定、コピー、貼り付けする方法についてご紹介します。

可視セルの判定は、『Hidden』を使うとできます。

可視セルのセル範囲の取得は、『SpecialCells』を使います。

コピーと貼り付けは、『Copy』と『PasteSpecial』でできます。

では、具体的なVBAコードを使って、解説していきます。

この記事で紹介すること

  • 可視セルの判定
  • 可視セルのコピーと貼り付け

目次から見たい項目へ移動すると便利ですよ。

目次

ポイントとなるVBAコード

初めに、可視セルの判定、コピー、貼り付けについてVBAコードをまとめておきます。

VBAコードだけ確認したい場合に、ご活用ください。

'行の可視セル判定
If ActiveSheet.Cells(3, 3).EntireRow.Hidden = True Then '非表示
If ActiveSheet.Cells(3, 3).EntireRow.Hidden = False Then '表示

'列の可視セル判定
If ActiveSheet.Cells(3, 3).EntireColumn.Hidden = True Then '非表示
If ActiveSheet.Cells(3, 3).EntireColumn.Hidden = False Then '表示

'可視セルのコピーと貼り付け
Set a = Worksheets("Sheet1").Range("A1").CurrentRegion '表のセル範囲を取得
Set a = a.SpecialCells(xlCellTypeVisible) '可視セルを取得
a.Copy '可視セルをコピー
Worksheets("Sheet2").Range("A1").PasteSpecial '貼り付け

では、解説していきます。

VBAで可視セルの判定(Hiddenを使う)

VBAで可視セルかどうかを判定するには、『Hidden』を使います。

  • 非表示の場合:『True』
  • 表示の場合:『False』

という感じで判定します。

『Hidden』は、行や列のどちらかに対して、判定します。

セルに対してではありません。

このHiddenの結果を使って、可視セルの判定をします。

行で可視セルを判定

まず、行で可視セルを判定してみます。

3行目を非表示にした表を用意しました。

3行目を非表示にした表

3行目を非表示にした表を用意

まず、非表示の行の判定をしてみます。

3行目が非表示かを判定

3行目が非表示かを確認するVBAコードです。

Sub TEST1()
    
    '3行目が非表示の場合
    If ActiveSheet.Cells(3, 3).EntireRow.Hidden = True Then
        MsgBox "非表示です"
    End If
    
End Sub

実行してみます。

3行目が非表示かを判定した結果

結果は、『非表示です』となりました。

3行目は、非表示となっているので、正しく判定できています。

次は、可視セルか、つまり表示しているかを確認してみます。

2行目が表示されているかを判定

2行目が表示されているかを確認するVBAコードです。

Sub TEST2()
    
    '3行目が表示の場合
    If ActiveSheet.Cells(2, 3).EntireRow.Hidden = False Then
        MsgBox "表示されています"
    End If
        
End Sub

実行してみます。

2行目が可視セルかを判定した結果

結果は、『表示しています』となりました。

2行目は、表示しているので、正しく判定できていますね。

というような感じで、行が可視セルかどうかを、判定できます。

列で可視セルを判定

次は、列で可視セルを判定してみます。

3列目を非表示にした表を用意しました。

3列目を非表示にした表

3列目を非表示にした表を用意

最初に、非表示の列の判定をしてみます。

3列目が非表示かを判定

3列目が非表示かを判定するVBAコードです。

Sub TEST3()
    
    '3列目が非表示の場合
    If ActiveSheet.Cells(3, 3).EntireColumn.Hidden = True Then
        MsgBox "非表示です"
    End If
        
End Sub

実行してみます。

3列目が非表示かを判定した結果

結果は、『非表示です』となりました。

3列目は、非表示となっているので、正しく判定できています。

次は、可視セルか、つまり表示しているかを確認してみます。

2列目が表示しているかを判定

2列目が表示しているかを確認するVBAコードです。

Sub TEST4()
    
    '3列目が表示の場合
    If ActiveSheet.Cells(3, 2).EntireColumn.Hidden = False Then
        MsgBox "表示されています"
    End If
    
End Sub

実行してみます。

2列目が可視セルかを判定した結果

結果は、『表示しています』となりました。

2列目は、表示しているので、正しく判定できていますね。

というような感じで、列が可視セルかどうかを、判定できます。

可視セルかを判定

可視セルかを判定する、というのをやってみます。

行や列ではなく、セルが表示されているかを判定します。

先ほどの、行と列の判定を組み合わせます。

3行目と3列目を、非表示にした表を用意しました。

3行目と3列目を非表示

3行目と3列目を非表示にした表

3行目と3列目が非表示となっています。

3行3列目のセルが可視セルか判定

3行3列目が、可視セルかを判定してみます。

Sub TEST5()
    
    '3行3列目のセル範囲を設定
    Set a = ActiveSheet.Cells(3, 3)
    
    flag = 0
    '3行目が表示
    If a.EntireRow.Hidden = False Then
        '3列目が表示
        If a.EntireColumn.Hidden = False Then
            flag = 1
        End If
    End If
    
    If flag = 1 Then
        MsgBox "表示されています"
    Else
        MsgBox "非表示です"
    End If
    
End Sub

実行してみます。

3行3列目が、可視セルかを判定した結果

結果は、『可視セルではありません』となりました。

行も、列も非表示となっていて、可視セルではありませんので、正解です。

2行2列目が可視セルか判定

2行2列目が、可視セルかを判定してみます。

Sub TEST6()
    
    '2行2列のセル範囲を設定
    Set a = ActiveSheet.Cells(2, 2)
    
    flag = 0
    '2行目が表示
    If a.EntireRow.Hidden = False Then
        '2列目が表示
        If a.EntireColumn.Hidden = False Then
            flag = 1
        End If
    End If
    
    If flag = 1 Then
        MsgBox "表示されています"
    Else
        MsgBox "非表示です"
    End If
    
End Sub

実行してみます。

2行2列目が、可視セルかを判定した結果

結果は、『可視セルです』となりました。

行も、列も表示となっていて、可視セルですので、正解です。

というような感じで、可視セルの判定には、行と列が表示かどうかをHiddenで判定します。

VBAで可視セルをコピーして貼付け

VBAで可視セルをコピーして貼付けする、というのをやってみます。

次の方法で紹介します。

  • SpecialCellsを使う
  • オートフィルタを使う

では、一つずつVBAコードを実行していきましょう。

SpecialCellsを使う

『SpecialCells』を使って、可視セルのみをコピーして貼り付けする方法です。

値のコピーと貼り付けは、CopyとPasteSpecialを使います。

可視セルをコピーして貼り付けするVBAコード

可視セルを、コピーして貼り付けするVBAコードです。

Sub TEST7()
    
    '表全体のセル範囲を取得
    Set a = Worksheets("Sheet1").Range("A1").CurrentRegion
    '可視セルのセル範囲を取得
    Set a = a.SpecialCells(xlCellTypeVisible)
    '可視セルをコピー
    a.Copy
    '可視セルを貼り付け
    Worksheets("Sheet2").Range("A1").PasteSpecial
    
End Sub

手順としては、

  • 表の範囲を取得(CurrentRegion)
  • 可視セルを取得(SpecialCells)
  • 可視セルをコピー(Copy)
  • 可視セルを貼り付け(PasteSpecial)

という感じです。

用意した表です。

3行目と3列目を非表示にした表

3行目と3列目が非表示になっています。

では、実行してみます。

可視セルのみをコピーして貼り付けした結果

可視セルのみをコピーして貼り付けすることができました。

可視セルの、値のみをコピーして貼り付け

ついでに、値だけを貼り付けしたい場合は、PasteSpecialの後ろに、xlPasteValueをつけます。

可視セルの、値のみをコピーして貼り付けするVBAコードです。

Sub TEST8()
    
    '表全体のセル範囲を取得
    Set a = Worksheets("Sheet1").Range("A1").CurrentRegion
    '可視セルのセル範囲を取得
    Set a = a.SpecialCells(xlCellTypeVisible)
    '可視セルをコピー
    a.Copy
    '可視セルの値のみを貼り付け
    Worksheets("Sheet2").Range("A1").PasteSpecial xlPasteValues
    
End Sub

手順は、

  • 表の範囲を取得(CurrentRegion)
  • 可視セルを取得(SpecialCells)
  • コピー(Copy)
  • 値のみを貼り付け(PasteSpecial)

という感じです。

先ほどと同じ表を使います。

3行目と3列目を非表示にした表

3行目と3列目が非表示になっています。

では、実行してみます。

可視セルのみをコピーして値のみを貼り付けした結果

可視セルのみをコピーして、値のみを貼り付けすることができました。

オートフィルタを使う

オートフィルタを使って、可視セルのみをコピーして貼り付けする方法です。

オートフィルタでフィルタして非表示にできるのであれば、断然こっちの方が簡単です。

可視セルのみをコピーして貼り付け

オートフィルタで可視セルのみを、コピーして貼り付けるVBAコードです。

Sub TEST9()
    
    '可視セルのセル範囲を取得
    Set a = Worksheets("Sheet3").Range("A1").CurrentRegion
    '可視セルをコピー
    a.Copy
    '可視セルを貼り付け
    Worksheets("Sheet4").Range("A1").PasteSpecial
    
End Sub

手順としては、

  • 可視セルのみのセル範囲を取得(CurrentRegion)
  • 可視セルをコピー(Copy)
  • 可視セルを貼り付け(PasteSpecial)

という感じです。

先ほどより簡単になりました。

では、先ほどと同じ表で確認してみます。

3行目と3列目を非表示にした表

3行目と3列目が非表示になっています。

では、実行してみます。

可視セルのみをコピーして貼り付けした結果

可視セルのみを、コピーして、貼り付けすることができました。

可視セルの、値のみをコピーして貼り付け

同じように、値のみをコピーして、貼り付けすることもできます。

オートフィルタで可視セルのみをコピーして、値のみを貼り付けるVBAコードです。

Sub TEST10()
    
    '可視セルのセル範囲を取得
    Set a = Worksheets("Sheet3").Range("A1").CurrentRegion
    '可視セルをコピー
    a.Copy
    '可視セルの値のみを貼り付け
    Worksheets("Sheet4").Range("A1").PasteSpecial xlPasteValues
    
End Sub

手順としては、

  • 可視セルのセル範囲を取得(CurrentRegion)
  • 可視セルをコピー(Copy)
  • 可視セルの値のみを貼り付け(PasteSpecial)

という感じです。

では、先ほどと同じ表で確認してみます。

3行目と3列目を非表示にした表

3行目と3列目が非表示になっています。

では、実行してみます。

可視セルのみをコピーして貼り付けした結果

可視セルのみを、コピーして、値のみを貼り付けすることができました。

可視セルの扱いは、オートフィルタを使うとかなり簡単になります。

おわりに

この記事では、可視セルを判定、コピー、貼り付けする方法についてご紹介しました。

可視セルの判定は、『Hidden』を使うとできます。

可視セルのセル範囲の取得は、『SpecialCells』を使います。

コピーと貼り付けは、『Copy』です。

可視セルのコピーと貼り付けは、オートフィルタを使うと簡単になります。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す