エクセルVBA100本ノック。3本目:セルの消去

この記事から得るもの

特定のセル(No)は残したまま、データ部だけを消す方法がわかる。
データの行数は任意でも同じようにデータだけ消去できる。


1 今回のお題

画像のように1行目に見出し、A列に№が入っています。
№行数およびデータ行数は毎回変化します。
この表の見出し(1行目)と№(A列)を残して、データ部分のみ値を消去してください。
※シートはアクティブシート

f:id:bimori466:20201104073208p:plain

excel-ubara.com

2 今回のお題の意図

行数の変化にも対応できるデータ部の削除。つまりこれも記録マクロでは作成不可能です。
記録マクロでは行数の変化に対応できません。
神髄先生の100本ノックは記録マクロでは作成不可能なものが基本なのかなと思いまいした。

3 回答

私の最初に作成した回答

Sub データ削除()

RowLastLine = Worksheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row
ColumnLastLine = Worksheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

Worksheets("sheet1").Range(Cells(2, 2), Cells(RowLastLine, ColumnLastLine)).ClearContents

End Sub

B列の最後、1行目の最後を取得して、cells(2,2)を起点として削除する方法です。

この回答を神髄先生にツイートしたところ、以下の指摘がありました。

シート指定しているところは良いのですが、その場合はRange内のCellsにも付けたほうが良いですね。

正直最初はどゆこと!?と思いました。結論から言うと以下のコードです。

添削後のコード

Sub データ削除2()

RowLastLine = Worksheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row
ColumnLastLine = Worksheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

Worksheets("sheet1").Range(Worksheets("sheet1").Cells(2, 2), Worksheets("sheet1").Cells(RowLastLine, ColumnLastLine)).ClearContents

End Sub

「Worksheets("sheet1").Range(Worksheets("sheet1").Cells(2, 2), Worksheets("sheet1").Cells(RowLastLine, ColumnLastLine))」この中のCellsもSheetを指定した方がいいよということでした。
正直、その必要あるの??と思いました。しかし、調べると不都合があるようです。

4 備考:Cellsで別のSheetは指定できるが…

例えば、以下のようにSheet名が異なるものを記述してみます。

Sub データ削除3()

RowLastLine = Worksheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row
ColumnLastLine = Worksheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

Worksheets("sheet1").Range(Worksheets("sheet2").Cells(2, 2), Worksheets("sheet2").Cells(RowLastLine, ColumnLastLine)).ClearContents

End Sub

Worksheets("sheet1").Range(Worksheets("sheet2").Cells(2, 2), Worksheets("sheet2")

RangeはSheet1なのに、CellsはSheet2です。実行するともちろんエラーになります。

f:id:bimori466:20201104080201p:plain

つまり、RangeもCellsも「Sheet」を選択できてしまうがゆえに、選択するSheetは同じにしておかなければならないということを神髄先生は伝えたかったのだと理解しました。

Range(Cells(),Cells())の書き方で、Cells部分にSheetを指定できるとは考えてもみませんでした。
でも、Sheet.Cells(1,1)という記述ができるので、そういうことですね。違うSheetを選択する可能性も否定できません。

勉強になりました!!

5 最終回答

Worksheets("sheet1")を何度も書くと、コードが長ったらしいので、Worksheetオブジェクトを使ってコードを短くします。

Sub データ削除4()

Dim Ws As Worksheet: Set Ws = Worksheets("sheet1")

RowLastLine = Ws.Cells(Rows.Count, 2).End(xlUp).Row
ColumnLastLine = Ws.Cells(1, Columns.Count).End(xlToLeft).Column

Ws.Range(Ws.Cells(2, 2), Ws.Cells(RowLastLine, ColumnLastLine)).ClearContents

End Sub

これが私の最終回答です。

6 参考になった回答

Kou Excelが大好きだ!さんの回答

Range("A1").CurrentRegion.Offset(1, 1).ClearContents

一行で書いてるやんけ~!!これが一番スマートな回答ですね。
Offsetはあまり使わないたちなんですが、これは使える!と思いました。