UsedRangeプロパティの使い方のコツ

こんにちは、じゅんぱ店長（@junpa33）です。

エクセルVBAでセル範囲を指定する方法はいくつかあります。

その中で今回は、UsedRangeというプロパティについてがテーマです。

「UsedRangeはメリットデメリットが共存していて、最終的には使いづらいプロパティだ。」

というような結論になる記事が多いですが、

本当のところはどうなのかを検証してみたいと思います。

UsedRangeプロパティを使う

UsedRangeというと、そんなに頻度が多く使われているというわけではありませんが、

上手く使えば、一発でセルのデータ範囲を指定することが出来るというものです。

UsedRangeの記述方法

UsedRangeは、Worksheetオブジェクトに対してだけ使えるプロパティです。

「Activesheet」「Worksheets(”シート名”)」「Worksheets(インデックス)」に対して利用できます。

UsedRangeの指定範囲を検証する

UsedRangeが指定する範囲は、

「何かの編集」が行われているセルは、すべてセル範囲に含まれることになります。

この【「何かの編集」とは何か 】を今回検証してみたいと思います。

Sub UsedRange検証コード()
　ActiveSheet.UsedRange.Select
End Sub

色々な「何かの編集」に対しての指定範囲

それぞれの場合についての検証を行っていきます。

デフォルトのシートに対して検証

デフォルトのシートの場合、検証コード実行するとセルA1が範囲指定されます。

セルに値が入力されている場合を検証

セルB2とセルE9に数値が入力されています。この範囲で範囲が指定されました。数式が入力されている場合も同様です。

セルに書式設定を行っている場合を検証

セルB2からB10まで「値の表示形式で日付」を設定します。最終行が10行目になり、範囲指定が変化しました。

Sub UsedRange付加コードA()
　Range("B2:B10").NumberFormatLocal = "yyyy/m/d"
End Sub

セルに罫線を設定した場合を検証

セルB2からセルD11まで罫線を設定します。罫線の設定により最終行が11行目になります。

Sub UsedRange付加コードB()
　Range("B2:D11").Borders.LineStyle = xlContinuous
End Sub

セルに背景色を設定した場合を検証

セルF12にピンク色の背景色を設定します。指定範囲が最終行が12行目、最右列がF列に変化しました。

Sub UsedRange付加コードC()
　Range("F12").Interior.ColorIndex = 7
End Sub

行高さのセルサイズを変更した場合を検証

セルA13のセル高さを「13.5（デフォルト）→18」に変更します。選択範囲が広がりました。

コード記述的にセルA13のセル高さに変更を加えたのですが、行の13行目自体に変更が加わったとみなされ、

セルA13そのものの編集とは見なされず、指定範囲には含まれませんでした。

Sub UsedRange付加コードD()
　Range("A13").RowHeight = 18
End Sub

列幅のセルサイズを変更した場合を検証

セルG15のセル幅を「8.38（デフォルト）→11」に変更します。

想定では、セルG13まで指定範囲が拡大すると考えましたが、列幅の変更に対しては変化なし、ノーリアクションの結果となりました。（複数回検証しましたので間違いないでしょう。エクセルバージョン2013）

Sub UsedRange付加コードE()
　Range("G15").ColumnWidth = 11
End Sub

指定範囲の解除　可逆させる方法

次は、一度設定されたUsedRangeの範囲設定を解除していけるかということを検証していきます。

エクセルシート上で範囲指定を設定したい時に、UsedRangeの場合は、セル位置を単に指定するだけでOKな別のプロパティとは性格が違います。

必要のないセルまでも一度操作してしまえば、そのセルの編集履歴までも消去しないと、そのセルは範囲指定に含まれてしまいます。

エクセルで資料を作成していて、UsedRangeを使った範囲指定が一番面倒で難しいケースは、写真⑫や写真⑭の時でではないでしょうか。

写真⑧の範囲指定の状態にまで戻すトライアル

写真⑧の状態にまで戻す方法を考えます。指定範囲を追加してきた逆を行います。

写真⑭の状態から逆行して戻していきます。

トライアル1

Sub UsedRange付加コードF()
　Range("G15").ColumnWidth = 8.38
　Range("A13").RowHeight = 13.5
　Range("F12").Interior.ColorIndex = False
End Sub

「結果は、変化なし」

付加コード実行の結果、シートの見た目はセル加工が戻され元に戻ったように見えますが、「UsedRangeの認識としては、セルの編集がされた状態のまま」だということです。

トライアル2

Sub UsedRange付加コードG()
　Range("G15").UseStandardWidth = True
　Range("A13").UseStandardHeight = True
　Range("F12").Interior.ColorIndex = False
End Sub

「結果は、セル高さのデフォルト設定に効果あり」

セルのサイズ変更については、記述のデフォルト設定のコードで、編集履歴もクリアされました。

セルの背景色の編集については「False」で外見上は初期状態に戻ったように見えますが、履歴は残っているようです。

トライアル3

Sub UsedRange付加コードH()
　Range("G15").UseStandardWidth = True
　Range("A13").UseStandardHeight = True
　Range("F12").ClearFormats
End Sub

「結果は、セル背景色クリアにはフォーマットの解除が必要」

「ClearFormats」を使うことで、セルの背景色の編集についての履歴もクリアされました。

写真⑧の状態に戻すためには、このトライアル3のVBAコードを使うことが必要ということが判明しました。

写真⑥の範囲指定の状態にまで戻すトライアル

写真⑥の状態にまで戻すには、付加コードHを実行した上で付加コードIを実行します。

Sub UsedRange付加コードI()
　ActiveSheet.UsedRange.Borders.LineStyle = xlLineStyleNone
End Sub

指定範囲内に対して「ClearFormatsメソッド」を実行すれば、完全に罫線の残骸エリアも消えて、データ入力セルだけの範囲設定になります。

データセルに設定した書式設定が不要の場合は、この「ClearFormatsメソッド」を利用してください。

データセルに設定した書式設定が必要な場合は、この「ClearFormatsメソッド」で書式設定を全削除してからの書式設定の再設定か、データ範囲はみ出し部分だけセル範囲指定して「ClearFormatsメソッド」を適用させることになります。

「Resizeプロパティ」で指定範囲を変更する方法もありますが、セルの罫線設定を行った痕跡は残りますので、UsedRangeプロパティの再利用は難しくなります。

Sub UsedRange検証コード2()
　ActiveSheet.UsedRange.Resize(8).Select
End Sub

写真②の範囲指定の状態にまで戻すトライアル

エクセルシートを写真②の初期状態にまで戻すのは簡単です。注意すべき点は、「Clearメソッド」だけでは、セルサイズをデフォルトに戻せないということです。

全クリアするためには、シートそのものをデフォルトに戻す必要があります。

Sub UsedRange付加コードJ()
　With ActiveSheet.Cells
    　.UseStandardWidth = True
    　.UseStandardHeight = True
    　.Clear
　End With
End Sub

検証結果　範囲設定の結論と可逆性の結論

範囲設定の検証結果

シート上のセルに対して、

が行われることで、それが全てUsedと見なされて範囲指定が変化してしまう。

一番注意が必要なのは、不用意にマウスでセルの大きさを変えてしまう事があげられる。

無意識化の場合もあるので、UsedRangeプロパティを使用している場合は、無駄なタイピングや頻繁にマウスでシートを触らないのが良いです。

可逆性の検証結果

変更されたセルサイズを元に戻す方法について、デフォルトサイズ指定でコードを記述しても、それも編集としてみなされる。何故かは不明だが列幅の変更については感知しなかった。

セルサイズについては、デフォルト設定のための専用コードを記述する必要がある。

書式設定の解除は、指定を戻すコードだけでは変更履歴を消すことは出来ないようだ。「False」でも履歴は消去できない。

「Clearメソッド」を使って変更履歴を消すことが出来ます。ただし、「ClearFormatsメソッド」だけを使っても、全ての書式が消去されてしまいますので、ある特定の書式設定は残したいという時は別の考えが必要です。

セル内の値と書式設定は残して、罫線だけは消したいという時は、「ClearFormatsメソッド」で書式設定も犠牲にするか、罫線の「xlLinestyleNone」でセルの編集履歴が残るか　等をケースバイケースで判断することになります。

関連するプロパティとメソッドをチェックする

関連の主要なプロパティ一覧

関連する主なプロパティは次のようなものです。

①Addressプロパティ

Sub UsedRangeAddressコード()
　Dim Ans As Variant
　　ActiveSheet.UsedRange.Select
　　Ans = ActiveSheet.UsedRange.Address
　　MsgBox Ans
End Sub

②Borderプロパティ

Sub UsedRangeBorderコード()
　ActiveSheet.UsedRange.Select
　ActiveSheet.UsedRange.Borders.LineStyle = xlContinuous
End Sub

③ColumnWidthプロパティ

Sub UsedRangeColumnWidthコード()
　ActiveSheet.UsedRange.Select
　ActiveSheet.UsedRange.ColumnWidth = 10
End Sub

④・⑬Count、Rowsプロパティ

Sub UsedRangeCountRowsコード()
　Dim Ans As Variant
　　ActiveSheet.UsedRange.Select
　　Ans = ActiveSheet.UsedRange.Rows.Count '相対位置を表示
　　MsgBox Ans
End Sub

⑤・⑦EntireRow、Hiddenプロパティ

Sub UsedRangeEntireRowHiddenコード()
　Dim Ans As Variant
　　ActiveSheet.UsedRange.Select
　　Ans = ActiveSheet.UsedRange.EntireRow.Hidden
　　MsgBox Ans
End Sub

⑥Heightプロパティ

Sub UsedRangeHeightコード()
　Dim Ans As Variant
　　ActiveSheet.UsedRange.Select
　　Ans = ActiveSheet.UsedRange.Height '選択範囲全体の高さ
　　MsgBox Ans
End Sub

⑧Interiorプロパティ

Sub UsedRangeInteriorコード()
　ActiveSheet.UsedRange.Select
　ActiveSheet.UsedRange.Interior.ColorIndex = 3
End Sub

⑨NumberFormatLocalプロパティ

Sub UsedRangeNumberFormatLocalコード()
　ActiveSheet.UsedRange.Select
　ActiveSheet.UsedRange.NumberFormatLocal = "yyyy/m/d"
End Sub

⑩Offsetプロパティ

Sub UsedRangeOffsetコード()
　ActiveSheet.UsedRange.Select
　ActiveSheet.UsedRange.Offset(9, 1).Select
End Sub

⑪Resizeプロパティ

Sub UsedRangeResizeコード()
　ActiveSheet.UsedRange.Select
　ActiveSheet.UsedRange.Resize(6).Select
End Sub

⑫RowHeightプロパティ

Sub UsedRangeRowHeight()
　ActiveSheet.UsedRange.Select
　ActiveSheet.UsedRange.RowHeight = 15
End Sub

⑭Valueプロパティ

Sub UsedRangeValueコード()
　ActiveSheet.UsedRange.Select
　ActiveSheet.UsedRange.Value = 555
End Sub

関連の主要なメソッド一覧

関連するメソッドは次のようなものです。

①Clearメソッド

Sub UsedRangeClesrメソッド()
　ActiveSheet.UsedRange.Clear
  ActiveSheet.UsedRange.Select
End Sub

②Copyメソッド

Sub UsedRangeCopyコード()
　ActiveSheet.UsedRange.Copy Range("D11")
　ActiveSheet.UsedRange.Select
End Sub

③Deleteメソッド

Sub UsedRangeDeleteコード()
　ActiveSheet.UsedRange.Delete
　ActiveSheet.UsedRange.Select
End Sub

④Insertメソッド

Sub UsedRangeInsertコード()
　ActiveSheet.UsedRange.Insert Shift:=xlDown
　ActiveSheet.UsedRange.Select
End Sub

⑤Selectメソッド

Selectメソッドについては、この記事の中でも頻繁に使用していますので、そちらで確認してください。

2020年11月14日 VBA 似た者SelectとActivateの違いを理解

まとめ

単にセル位置を指定するだけで範囲指定が出来るほかのプロパティに比べて、

UsedRangeプロパティは、はっきり言って　” 面倒くさい ”　タイプに含まれます。

ただ、上手に使えば、空欄や空白行などに阻害されることなく、一発で範囲指定をすることが可能になります。

この魅力を踏まえて、上手く使える方法を考えていくのもVBAプログラムの組み立ての楽しみにもなります。

一度はチャレンジしてみてはどうでしょう。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

2022年1月9日 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう！

エクセルVBA業務ツールで日常の業務改善を行いましょう。

＜記事内容についての告知＞

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

Dstyle web