VBA100本ノック 4本目：セルの消去

公開日：2020-10-22　最終更新日：2021-08-25

VBA100本ノック 4本目：セルの消去

セルの消去に関する問題です。
定数値のセルだけ値を消去します。

ツイッター連動企画です。
ツイート文字数制限があるので、ブック指定等省略可能な記述は省略しています。

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。

マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。

出題

#VBA100本ノック 4本目
画像のように1行目に見出し、A列に№が入っています。
この表範囲の一部には計算式が入っています。
（画像の最下行とD列には数式が入っています。）
データ行数は毎回変化します。
見出し行とA列№と計算式は残し、定数値だけを消去してください。
※画像ならB2:C11を消去

サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_04.xlsm
https://excel-ubara.com/vba100sample/VBA100_04.zip

VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。

他の人の回答および解説を見て、書いたVBAを見直してみましょう。

頂いた回答

出題ツイートの引用ツイートでの回答

解説

シート指定は省略


  
    
      Dim rng As Range

Set rng = Range("A1").CurrentRegion.Offset(1, 1)

On Error Resume Next

      rng.SpecialCells(xlCellTypeConstants).ClearContents

SpecialCellsは、該当セルが存在しない場合エラーとなるのでOn Errorで対応します。

計算式が入っている列・行が特定できる場合は、ResizeまたはIntersectで範囲を絞ってもよいでしょう。

消去する条件によっては1セルずつ判定しなければならない場合もあるかもしれません。
そのような場合は、HasFormulaプロパティで計算式の有無を判定できます。
続きは、記事の補足で。

補足

ResizeまたはIntersectを使って範囲を特定する場合の書き方は色々な書き方ができます。
例えば、表範囲の外側の行・列を除いた範囲を求めたい場合は、
Offset(1, 1)とOffset(-1, -1)、このIntersectで作ることができます。
しかし、開始点がA列または1行目の場合は(-1, -1)がエラーになってしまうので、一工夫必要になります。


  
    
      With Range("A1").CurrentRegion

      　　With Intersect(.Cells, .Offset(1, 1))

      　　　　Intersect(.Cells, .Offset(-1, -1)).ClearContents

      　　End With

      End With

計算式が入っている列が飛び飛びだったりした場合でも、当該行列を別途求めるようにVBAを書けばできますが少し面倒ですね。
何より、そもそも数式が入っている列がはっきりしないような場合は、セルを1つずつ判定することになってしまいます。


  
    
      Dim rng As Range

For Each rng In Range("A1").CurrentRegion.Offset(1, 1)

      　　If Not rng.HasFormula Then

　　　　rng.ClearContents

　　End If

Next

単に定数値というだけではなく別の条件もあるような場合は、HasFormulaで判定しなければならない場合も出てくると思います。

SpecialCells(xlCellTypeConstants)
これで定数値の入っているセルだけを取得することができます。

ただしSpecialCellsでは、該当するセルが存在しない場合はエラーになってしまいます。

そこで、On Errorで対処しておく必要があります。

SpecialCellsの引数は、「ジャンプ」→「選択オプション」の画面と対比させて覚えてください。

SpecialCellsの引数

XlCellType列挙 を指定します。

名前	説明
xlcelltypecomments	コメント
xlcelltypeconstants	定数、Valueオプションが指定できます。
xlcelltypeformulas	数式、Valueオプションが指定できます。
xlcelltypeblanks	空白セル
xlcelltypelastcell	最後のセル
xlcelltypevisible	可視セル
xlcelltypeallformatconditions	条件付き書式 - すべて
xlcelltypesameformatcondition	条件付き書式 - 同じ入力規則
xlcelltypeallvalidation	データの入力規則 - すべて
xlcelltypesamevalidation	データの入力規則 - 同じ入力規則

サイト内関連ページ

第28回．セル・行・列の選択（Select,ActivateとCurrentRegion）｜VBA入門

・選択セルとアクティブセル・セルの選択・セルをアクティブにする・行の選択、列の選択・セル領域の選択・メソッドとはプロパティとは

第39回．セルのクリア（Clear,ClearContents）｜VBA入門

・セル(Rangeオブジェクト)のクリア関係のメソッド一覧・Range.Clear ・Range.ClearContents ・クリア関係メソッドについて・メソッド実行がエラーになる場合

第8回．表範囲をまとめて消去する（OffsetとClearContents）｜VBA再入門

・見出しを残しデータ部分のみ消去・Offsetを使って範囲をずらす・データ範囲に関係なく消去する場合・ExcelマクロVBA入門等の対応ページ

第135回．ジャンプの選択オプション（SpecialCells）｜VBA入門

・ジャンプの選択オプションとは・RangeオブジェクトのSpecialCellsメソッド・SpecialCellsの使用例

同じテーマ「VBA100本ノック」の記事

1本目：セルのコピー
 2本目：セルのコピー
 3本目：セルの消去
4本目：セルの消去
5本目：セルの計算
 6本目：セルに計算式
 7本目：日付データの扱い
 8本目：点数の合否判定
 9本目：フィルターコピー
 10本目：行の削除
 11本目：セル結合の警告

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
マクロVBA入門編
VBA100本ノック
4本目：セルの消去

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

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

VBA練習問題
VBA100本ノック 4本目：セルの消去