VBA再入門
第8回.表範囲をまとめて消去する(OffsetとClearContents)

マクロが覚えられないという初心者向けに理屈抜きのやさしい解説
最終更新日:2019-12-12

第8回.表範囲をまとめて消去する(OffsetとClearContents)


エクセルのマクロVBAでの処理では、表範囲を一旦クリアしてから処理することが多くあります。
つまり、表範囲を初期化してから、その後に何らかの処理をするという事になります。


しかし、表範囲をクリアすると言っても、表そのものを消してしまう訳にはいかない場合が多く、
データ部分、つまり見出しや計算式の入ってないセルのみを消去しなければなりません。

以下のような表でやってみましょう。

エクセル マクロVBA サンプルデータ

見出しを残しデータ部分のみ消去

上の表の、2行目から下の部分をクリアします。
つまり、
見出しだけを残して初期化します。

それには、まず、表範囲を確実に把握することが必要です。
表の最終行を取得して、2行目から最終行までを範囲とすることができそうです。
そして、セル範囲の消去は、

セル範囲.ClearContents
または、
セル範囲.Clear


これになります。

Clearは書式も消して、何も設定されていない初期のセルにします。
対して、
ClearContentsは、値のみの消去になります、セル範囲を選択して、Deleteキーを押す操作になります。

今回は、ClearContentsでコードを説明します。

エクセル マクロ VBAコード

上記の表ならこれで問題はありません。
iには、最終行が取得されて11が入りますので、
結果として、
Range(Range("A2"), Cells(11, 3)).ClearContents
という事なので、正しく消去できます。

しかし、上記のコードを2回実行すると、どうなるでしょうか。
1回目で、データ部分が消去され、

マクロ VBA サンプル画像

このようになりますので、
2回目には、最終行が1となり、
Range(Range("A2"), Cells(1, 3)).ClearContents
となってしまいます。
これは、実行してみれば分かりますが、1行目の見出しまで消えてしまいます
これを避けるためには、最終行が2以上の時だけ消去する等のロジックを入れる必要があります。

Offsetを使って範囲をずらす

しかし、VBAでは、もっと簡単に表範囲のデータ部分だけ消去する方法があります。

エクセル マクロ VBAコード


たったこれだけで、表範囲のデータ部分のみ消去出来るのです。
では、なぜそのようになるかの理屈です、コードの解説をします。

Range("A1").CurrentRegion
ここまでで、表全体の範囲(A1:C11)になります。
これは、A1セルを選択し、Ctrl+Aを押した範囲です。
カレントリージョンと読みます、
Offset(1, 0)
これは、その前に書かれている範囲をずらす命令です。
Offset(ずらす行数, ずらす列数)
したがって、
Range("A1").CurrentRegion.Offset(1, 0)
これは、表全体の範囲(A1:C11)を下に1つずらした範囲(A2:C12)になります。

これなら正しく消去できます。
2回目を実行したときも、
表全体の範囲(A1:C1)を下に1つずらした範囲(A2:C2)となるので問題ありません。

ちなみに、消去する範囲が1行余分なのですが、
CurrentRegionの範囲は、周り(上下左右)のセルには何も入っていない独立したセル範囲なので問題ないのです。

では、上記の表で、店舗名を消したくない時はどうしたらよいでしょうか。
ちょっと考えてみて下さい。
Offset(ずらす行数, ずらす列数)
なので、右にも1つずらせば良いのです。

エクセル マクロ VBAコード

これで、1行目とA列を残したデータ部分を消去できます。

データ範囲に関係なく消去する場合

Cells.ClearContents '・・・ 全セルを消去
Columns(1).ClearContents '・・・ A列を消去
Range("A:B").ClearContents '・・・ A:B列を消去
Rows(1).ClearContents '・・・ 1行目を消去
Range("1:2").ClearContents '・・・ 1:2行目を消去


ExcelマクロVBA入門等の対応ページ

第10回.Range以外の指定方法(Cells,Rows,Columns)
Rangeの指定で、あらゆるセルおよびセル範囲は指定できるのですが、マクロで使う場合は、ちょっと使いづらい場合があります。"A1"や"B5"と言うような文字で指定するのでは何かと不便です、もっと、プログラムっぽい(笑)指定方法があります。

第28回.セル・行・列の選択(Select,ActivateとCurrentRegion)
マクロVBAで、セル、行、列を選択するときの記述について説明します。まずは、セルの選択について説明する前に、選択セルとアクティブセルについて、説明する必要があります。選択セルとアクティブセル この状態で、選択セルは、B2~C6の10個のセルです。

第39回.セルのクリア(Clear,ClearContents)
セルをクリアするマクロVBAの書き方です、クリアするといっても、セルの何を(値、色、コメント等々)クリアするかによって、VBAコードが違ってきます。具体的には、セルの何を(値、色、コメント等々)クリアするかによって使用するプロパティが変わるという事です。

第83回.RangeのOffsetプロパティ
Offsetプロパティは、指定されたセル範囲(Rangeオブジェクト)をオフセット(移動)します、オフセット(移動)したセル範囲を表すRangeオブジェクトを返します。Offsetとは、「差し引きする」意味ですが、Offsetプロパティで取得されるのは、元のRange範囲を、指定した行数・列数移動したRange範囲になります。



同じテーマ「VBA再入門」の記事

第5回.同じ計算を行数分繰り返す(For~Next)
第6回.表の先頭から最終行まで繰り返す(ForとEnd(xlUp).Row)
第7回.セルの値によって計算を変える(Ifステートメント)
第8回.表範囲をまとめて消去する(OffsetとClearContents)
第9回.関数という便利な道具(VBA関数)
第10回.ワークシートの関数を使う(WorksheetFunction)
第11回.分からない事はエクセルに聞く(マクロの記録)
第12回.エクセルの言葉を理解する(オブジェクト、プロパティ、メソッド)
第13回.セルのコピペ方法を知る(CopyとPaste、さらに)
第14回.セルの書式を設定する(NumberFormatLocal,Font,Barders,Interior)
第15回.手作業で出来なければマクロは書けない


新着記事NEW ・・・新着記事一覧を見る

トランザクション処理|SQL入門(12月11日)
インデックスを作成して高速化(CREATE INDEX)|SQL入門(12月9日)
他のテーブルのデータで追加/更新/削除|SQL入門(12月8日)
データの削除(DELETE)|SQL入門(12月7日)
データの更新(UPDATE)|SQL入門(12月6日)
複数のSELECT結果を統合(UNION,UNION ALL)|SQL入門(12月5日)
テーブルを結合して取得(INNER JOIN,OUTER JOIN)|SQL入門(12月4日)
データベースの正規化とマスタの作成|SQL入門(12月3日)
データベースにおけるNULLの扱い方|SQL入門(12月2日)
オブジェクト変数とは何か|VBA技術解説(12月2日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • 表範囲をまとめて消去する(OffsetとClearContents)

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
    掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
    本文下部へ