EXCEL VBA 色んな方法でセルのデータを削除・複数方法を紹介・表題・タイトルを残す　（テクニック）

2023年3月12日

EXCEL VBA 色んな方法でセルのデータを削除・複数方法を紹介・表題・タイトルを残す　（テクニック）

●はじめに

今回説明するのは、EXCEL　VBAで表やデータなどをクリアーする時に、便利な使い方を説明いたします。EXCELで作成した表は繰り返し利用するので、この時に全てクリアしますと表などを再利用できないので、部分的な削除や選択した範囲のみクリアする方法などを順番に説明いたします。

●【 Clear ClearContents ClearFormats（セルの値・書式のクリア、下記を参照して下さい】

EXCEL VBA Clear ClearContents ClearFormats（セルの値・書式のクリア）

●【Range.Clear メソッド (Excel)、下記を参照して下さい】（Microsoft社　様）】
https://learn.microsoft.com/ja-jp/office/vba/api/excel.range.clear

●書式の説明　（Rannge.Clearメゾット）

●　Excel VBAのRange.Clearメソッドは、指定した範囲内のすべてのデータ（値、書式、コメント、ハイパーリンク）を削除するために使用されます。このメソッドを使用すると、範囲内のセルにあるデータをすばやくクリアできます。

引数	説明（内容）
ClearContents	省略可能なブール型の引数で、Trueを指定すると、セル内のデータ（値、数式）をクリアします。Falseを指定すると、値、数式は削除されません。
ClearFormats	省略可能なブール型の引数で、Trueを指定すると、セル内の書式をクリアします。Falseを指定すると、書式は削除されません。
ClearHyperlinks	省略可能なブール型の引数で、Trueを指定すると、セル内のハイパーリンクをクリアします。Falseを指定すると、ハイパーリンクは削除されません。

【使用例】

Sub ClearRange()
    'A1:C5の範囲内のデータと書式とハイパーリンクをクリアする
    Range("A1:A5").ClearContents 'セル内のデータ（値、数式）をクリアします。
    Range("B1:B5").ClearFormats　'セル内の書式をクリアします。　
    Range("C1:C5").ClearHyperlinks  'セル内のハイパーリンクをクリアします。
End Sub

Sub ClearRange()

'A1:C5の範囲内のデータと書式とハイパーリンクをクリアする

Range("A1:A5").ClearContents 'セル内のデータ（値、数式）をクリアします。

Range("B1:B5").ClearFormats　'セル内の書式をクリアします。　

Range("C1:C5").ClearHyperlinks 'セル内のハイパーリンクをクリアします。

End Sub

【注意点】
上記の方法でセル内のハイパーリンクをクリアが上手くいかない場合は、下記の方法でクリアする事もできます。

Range(“C1:C5”).Hyperlinks.Delete

EXCEL VBA 表の表題・見出し以下のデータをクリアー（削除する）　（テクニック）

●プログラム説明　(サンプル①)

以下のサンプルプログラムは、Excelで表示されている表の表題部分のみを残して、表のデータ部分をクリアするためのサンプルプログラムを2つ用意しました。業務上、このような様々な表を利用することが多いと思います。ワークシートにある表を再利用する際には、これらのプログラムはとても便利です。以下、それぞれのプログラムの説明をします。

【プログラムの流れ】
●　ClearData01の場合
①　ワークシートオブジェクトを取得し、ws変数に代入します。※ワークシート「Sheet1」
②　ws変数のRangeプロパティを使用して、A2セルを起点として、そのセルを含む現在の領域を指定する。この場合、2行目以降の範囲を取得するために、”2:” &ws.Rows.Countという範囲指定方法が使用されている。
③　Offsetプロパティを使用して、領域の1行目を除外する。
④　最後に、ClearContentsメソッドを使用して、その領域内のセルの内容を削除する。

●　ClearData02の場合
ワークシートオブジェクトを取得し、ws変数に代入する。※ワークシート「Sheet1」
ws変数のRangeプロパティを使用して、A2セルを起点として、そのセルを含む現在の領域を指定する。この場合、CurrentRegionプロパティを使用しています。
Offsetプロパティを使用して、領域の1行目を除外する。
最後に、ClearContentsメソッドを使用して、その領域内のセルの内容を削除する。

【プログラム実行条件】
・ワークシート「Sheet1」を作成する必要があります。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　ClearData01（サンプルプログラム）

'以下のコードを使用して、ワークシート「Sheet1」の2行目以降のすべてのセルの内容を削除できます。

'このコードでは、ワークシートオブジェクトを取得し、そのオブジェクトの範囲プロパティを使用して、2行目から最終行までの範囲を指定しています。そして、ClearContentsメソッドを使用して、その範囲内のセルの内容を削除しています。

Sub ClearData01()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    ws.Range("2:" & ws.Rows.Count).ClearContents
End Sub
'


'このコードでは、ワークシートオブジェクトを取得し、そのオブジェクトの範囲プロパティを使用して、A2セルを起点として、そのセルを含む現在の領域を指定しています。そして、Offsetプロパティを使用して、領域の1行目を除外しています。最後に、ClearContentsメソッドを使用して、その領域内のセルの内容を削除しています。

Sub ClearData02()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    ws.Range("A2").CurrentRegion.Offset(1).ClearContents
End Sub
'

'以下のコードを使用して、ワークシート「Sheet1」の2行目以降のすべてのセルの内容を削除できます。

'このコードでは、ワークシートオブジェクトを取得し、そのオブジェクトの範囲プロパティを使用して、2行目から最終行までの範囲を指定しています。そして、ClearContentsメソッドを使用して、その範囲内のセルの内容を削除しています。

Sub ClearData01()

Dim ws As Worksheet

Set ws = Sheets("Sheet1")

ws.Range("2:" & ws.Rows.Count).ClearContents

End Sub

'このコードでは、ワークシートオブジェクトを取得し、そのオブジェクトの範囲プロパティを使用して、A2セルを起点として、そのセルを含む現在の領域を指定しています。そして、Offsetプロパティを使用して、領域の1行目を除外しています。最後に、ClearContentsメソッドを使用して、その領域内のセルの内容を削除しています。

Sub ClearData02()

Dim ws As Worksheet

Set ws = Sheets("Sheet1")

ws.Range("A2").CurrentRegion.Offset(1).ClearContents

End Sub

●実行前～実行後　※上記プログラムいずれか実行することで、ワークシート「Sheet1」の2行目以降のセルの内容を削除することができます。どちらのコードも同じ処理を行っているため、どちらを使用しても構いません。
（画面クリックして拡大）

EXCEL VBA 表の表題・見出し以下のデータをクリアー・　AutoFilterを使ったデータの削除・該当の行の削除　（テクニック）

●プログラム説明　(サンプル②)

以下のサンプルプログラムは、Excelで表示されている表の表題部分のみを残して、表のデータ部分をクリアするためのサンプルプログラムです。サンプルプログラム①と同じ処理ですが、今回は、AutoFilterを使った方法で説明します。また、オートフィルターの特徴として指定したデータを削除する方法も併せて説明いたします。それでは、順番に説明いたします。

●　DeleteFilteredData01の場合(実行前～実行後）
　　※データが登録されて部分がフィルタリングされてA列を除いて削除されます。

【プログラムの流れ】
●　DeleteFilteredData01の場合
①　ActiveSheet.AutoFilterMode = False　⇒ アクティブシートのフィルタを解除します。
②　Range(“A1”).AutoFilter　⇒　セルA1をフィルタするように設定します。
③　Range(“A1″).AutoFilter Field:=1, Criteria1:=”<>”, VisibleDropDown:=False　⇒　セルA1の列（Field:=1）において、空白ではないセルを表示するように設定します。VisibleDropDown:=Falseにより、ドロップダウンリストを表示しなくなります。
④　Range(“A2:A” & Cells(Rows.Count, “A”).End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete　⇒セルA2から最終行まで、フィルタリングされたセルを削除します。SpecialCells(xlCellTypeVisible)は、フィルタリングされたセルのみを取得するために使用されます。EntireRow.Deleteは、行全体を削除するために使用されます。
⑤　ActiveSheet.AutoFilterMode = False　⇒　アクティブシートのフィルタを解除します。

●　DeleteFilteredData02の場合(実行前～実行後）
　　※データが登録されて部分がフィルタリングされてA列を除いて削除されます。

●　DeleteFilteredData02の場合
①　ActiveSheet.AutoFilterMode = False　⇒　アクティブシートのフィルタを解除します。
②　Range(“A1”).AutoFilter　⇒　シートのセルA1をフィルタするように設定します。
③　Range(“A1″).AutoFilter Field:=1, Criteria1:=”神奈川支店”, VisibleDropDown:=False　⇒セルA1の列（Field:=1）において、神奈川支店のセルを表示するように設定します。VisibleDropDown:=Falseにより、ドロップダウンリストを表示しなくなります。
④　Range(“A2:A” & Cells(Rows.Count, “A”).End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete　⇒　セルA2から最終行まで、フィルタリングされたセル（神奈川支店のセル）を削除します。SpecialCells(xlCellTypeVisible)は、フィルタリングされたセルのみを取得するために使用されます。EntireRow.Deleteは、行全体を削除するために使用されます。
⑤　ActiveSheet.AutoFilterMode = False　⇒　アクティブシートのフィルタを解除します。

【プログラム実行条件】
・ワークシート「Sheet1」を作成する必要があります。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　ClearData02（サンプルプログラム）

'
'AutoFilterを使用して、フィルタリングされたデータのみを削除することもできます。
Sub DeleteFilteredData01()
    ActiveSheet.AutoFilterMode = False 'フィルタを解除
    Range("A1").AutoFilter 'A列をフィルタする
    Range("A1").AutoFilter Field:=1, Criteria1:="<>", VisibleDropDown:=False '空白ではないセルを表示する
    Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'フィルタされたセルを削除
    ActiveSheet.AutoFilterMode = False 'フィルタを解除
End Sub
'
'--------------------------------------------------------------------------------------------------------------------------
'
'AutoFilterを使用して、フィルタリングされたデータ【神奈川支店】のみを削除することもできます。
Sub DeleteFilteredData02()
    ActiveSheet.AutoFilterMode = False 'フィルタを解除
    Range("A1").AutoFilter 'A列をフィルタする
    Range("A1").AutoFilter Field:=1, Criteria1:="神奈川支店", VisibleDropDown:=False '神奈川支店のセルを表示する
    Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'フィルタされたセルを削除
    ActiveSheet.AutoFilterMode = False 'フィルタを解除
End Sub
'

'AutoFilterを使用して、フィルタリングされたデータのみを削除することもできます。

Sub DeleteFilteredData01()

ActiveSheet.AutoFilterMode = False 'フィルタを解除

Range("A1").AutoFilter 'A列をフィルタする

Range("A1").AutoFilter Field:=1, Criteria1:="<>", VisibleDropDown:=False '空白ではないセルを表示する

Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'フィルタされたセルを削除

ActiveSheet.AutoFilterMode = False 'フィルタを解除

End Sub

'--------------------------------------------------------------------------------------------------------------------------

'AutoFilterを使用して、フィルタリングされたデータ【神奈川支店】のみを削除することもできます。

Sub DeleteFilteredData02()

ActiveSheet.AutoFilterMode = False 'フィルタを解除

Range("A1").AutoFilter 'A列をフィルタする

Range("A1").AutoFilter Field:=1, Criteria1:="神奈川支店", VisibleDropDown:=False '神奈川支店のセルを表示する

Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'フィルタされたセルを削除

ActiveSheet.AutoFilterMode = False 'フィルタを解除

End Sub

●実行前～実行後　※プログラム実行後【Sub DeleteFilteredData01】では、A列の表題部分を残してデータ部分が削除されました。また、【Sub DeleteFilteredData02】では「神奈川支店」のデータ（行）を削除されます。
（画面クリックして拡大）

●AutoFilterを使用して、フィルタリングされたデータのみを削除することもできます。
【Sub DeleteFilteredData01】

●AutoFilterを使用して、フィルタリングされたデータ【神奈川支店】のみを削除することもできました。
【Sub DeleteFilteredData02】

EXCEL VBA 表の表題・見出し以下のデータをクリアー・　Findメゾットを使ったデータの削除・該当の行の削除　（テクニック）

●プログラム説明　(サンプル③)

以下のサンプルプログラムは、上記のサンプルプログラムとほぼ同じ勝利を行いますが、今回は、Findメゾット使って指定した該当データ部分の行を削除するサンプルプログラムを説明したいと思います。

【プログラムの流れ】

①　使用されている範囲を取得し、変数rngに格納します。
②　検索するテキストを変数searchTextに格納します。【ワークステーション】が対象
③　rng内で最初に検索されたセルをcに格納します。もし、特定のテキストを含む行が見つからない場合は、cにはNothingが格納されます。
④　変数firstAddressに、cのアドレスを格納します。
⑤　c.EntireRowを変数excludeRowsに格納します。これにより、最初に見つかった行は除外されます。
⑥　Doループを使用して、テキストを含む行を検索していきます。その際、最初に見つかった行は、すでに除外されているため、次の行から検索が開始されます。
⑦　テキストを含む行を見つけた場合、excludeRowsにその行を追加します。
⑧　次の検索対象を取得するために、FindNextメソッドを使用します。
⑨　ループを終了するための条件を確認します。もし、次に検索する行が存在しない場合、または最初に見つかった行に戻った場合、ループを終了します。
⑩　excludeRowsに格納された行を削除します。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　ClearData03（サンプルプログラム）

'
'
Sub DeleteRowsContainingText01()  'Findメゾットを利用したデータ削除
    ' 使用されている範囲を取得し、変数rngに格納
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange
    
    ' 検索するテキストを変数searchTextに格納
    Dim searchText As String
    searchText = "ワークステーション"
    
    ' rng内で最初に検索されたセルをcに格納
    Dim c As Range
    Set c = rng.Find(searchText, LookIn:=xlValues)
    
    ' 特定のテキストを含む行が見つかった場合
    If Not c Is Nothing Then
        ' 最初に見つかったセルのアドレスを変数firstAddressに格納
        Dim firstAddress As String
        firstAddress = c.Address
        
        ' 最初に見つかった行を除外するため、c.EntireRowをexcludeRowsに格納
        Dim excludeRows As Range
        Set excludeRows = c.EntireRow
        
        ' テキストを含む行を検索していく
        Do
            ' excludeRowsにテキストを含む行を追加
            Set excludeRows = Union(excludeRows, c.EntireRow)
            
            ' 次の検索対象を取得するために、FindNextメソッドを使用
            Set c = rng.FindNext(c)
            
            ' ループを終了するための条件を確認
            If c Is Nothing Or c.Address = firstAddress Then
                Exit Do
            End If
        Loop
        
        ' excludeRowsに格納された行を削除
        excludeRows.Delete
    End If
End Sub
'

Sub DeleteRowsContainingText01() 'Findメゾットを利用したデータ削除

' 使用されている範囲を取得し、変数rngに格納

Dim rng As Range

Set rng = ActiveSheet.UsedRange

' 検索するテキストを変数searchTextに格納

Dim searchText As String

searchText = "ワークステーション"

' rng内で最初に検索されたセルをcに格納

Dim c As Range

Set c = rng.Find(searchText, LookIn:=xlValues)

' 特定のテキストを含む行が見つかった場合

If Not c Is Nothing Then

' 最初に見つかったセルのアドレスを変数firstAddressに格納

Dim firstAddress As String

firstAddress = c.Address

' 最初に見つかった行を除外するため、c.EntireRowをexcludeRowsに格納

Dim excludeRows As Range

Set excludeRows = c.EntireRow

' テキストを含む行を検索していく

' excludeRowsにテキストを含む行を追加

Set excludeRows = Union(excludeRows, c.EntireRow)

' 次の検索対象を取得するために、FindNextメソッドを使用

Set c = rng.FindNext(c)

' ループを終了するための条件を確認

If c Is Nothing Or c.Address = firstAddress Then

Exit Do

End If

Loop

' excludeRowsに格納された行を削除

excludeRows.Delete

End If

End Sub

●実行前～実行後　※プログラム実行後、削除対象と設定した「ワークステーション」に該当する行が削除されました。
（画面クリックして拡大）

EXCEL VBA 表の表題・見出し以下のデータをクリアー・　Findメゾットを使った指定した複数データの削除・該当の行の削除　（テクニック）

●プログラム説明　(サンプル④)

下記のサンプルプログラムは、サンプルプログラム③の応用となります。サンプルプログラム③と同じくFindメゾットを利用したデータの削除ですが、今回の場合は複数の指定した文字列に対して該当する行を一括で削除するサンプルプログラムです。

【プログラムの流れ】
①　ActiveSheet.UsedRangeを使って、アクティブなシートで使用されている範囲を取得し、rngという名前の変数に格納します。
②　searchTextsという名前の配列変数を宣言し、配列の要素に”埼玉支店”、”阿部”、”タブレット”を格納します。
③　For Eachループを使って、searchTextsに格納されているテキストを順に検索します。
④　Findメソッドを使用して、searchTextで指定されたテキストがrng内で最初に見つかったセルをcという名前の変数に格納します。
⑤　Ifステートメントを使って、cがNothingではない場合には以下の処理を実行します。
⑥　c.Addressを使って、cのアドレスをfirstAddressという名前の変数に格納します。
⑦　EntireRowプロパティを使って、cが属する行全体をexcludeRowsという名前の範囲オブジェクトに格納します。
⑧　Doループを使って、cが含まれる行を検索して、excludeRowsに追加します。
⑨　Union関数を使って、excludeRowsとc.EntireRowを結合して、excludeRowsに再び代入します。
⑩　FindNextメソッドを使って、cの次の出現箇所を検索し、cに代入します。
⑪　Ifステートメントを使って、cがNothingであるか、またはc.AddressがfirstAddressである場合、Doループを終了します。
⑫　excludeRowsに格納された行を削除します。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　ClearData04（サンプルプログラム）

'
'
Sub DeleteRowsContainingMultipleTexts01()
    ' 使用されている範囲を取得し、変数rngに格納する
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange
    
    ' 検索するテキストを配列に格納する
    Dim searchTexts As Variant
    searchTexts = Array("埼玉支店", "阿部", "タブレット")
    
    ' 各テキストを順番に検索して、除外する
    Dim searchText As Variant
    For Each searchText In searchTexts
        Dim c As Range
        Set c = rng.Find(searchText, LookIn:=xlValues)
        
        ' 特定のテキストが見つかった場合
        If Not c Is Nothing Then
            ' 最初に見つかった行は、すでに除外されているため、excludeRowsに追加する
            Dim firstAddress As String
            firstAddress = c.Address
            Dim excludeRows As Range
            Set excludeRows = c.EntireRow
            
            ' テキストを含む行を検索していく
            Do
                ' excludeRowsに、検索された行を追加する
                Set excludeRows = Union(excludeRows, c.EntireRow)
                
                ' 次の検索対象を取得する
                Set c = rng.FindNext(c)
                
                ' ループを終了する条件を確認する
                If c Is Nothing Or c.Address = firstAddress Then
                    Exit Do
                End If
            Loop
            
            ' excludeRowsに格納された行を削除する
            excludeRows.Delete
        End If
    Next searchText
End Sub
'
'