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

vbausedrangeeyecatch

Excel VBAのセルの範囲指定で、UsedRangeプロパティがあります。
セルの編集履歴まで選択範囲に含める特徴があり、今回この使い方を検証してみました。

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

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

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

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

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

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

UsedRangeプロパティを使う

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

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

UsedRangeの記述方法

構文
  • Worksheetオブジェクト.UsedRange

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

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

UsedRangeの指定範囲を検証する

UsedRangeが指定する範囲は、

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

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

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

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

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

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

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

vbausedrange001
①デフォルトのシート
vbausedrange002
②検証コードの実行後

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

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

vbausedrange003
③セルに値が入力されている
vbausedrange004
④検証コード実行後

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

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

付加コードA
Sub UsedRange付加コードA()
 Range("B2:B10").NumberFormatLocal = "yyyy/m/d"
End Sub
vbausedrange005
⑤写真③のシートに対して付加コードを実行
vbausedrange006
⑥検証コードの実行後

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

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

付加コードB
Sub UsedRange付加コードB()
 Range("B2:D11").Borders.LineStyle = xlContinuous
End Sub
vbausedrange007
⑦写真⑤のシートに対して付加コードを実行
vbausedrange008
⑧検証コードの実行後

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

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

付加コードC
Sub UsedRange付加コードC()
 Range("F12").Interior.ColorIndex = 7
End Sub
vbausedrange009
⑨写真⑦のシートに対して付加コードを実行
vbausedrange010
⑩検証コードの実行後

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

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

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

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

付加コードD
Sub UsedRange付加コードD()
 Range("A13").RowHeight = 18
End Sub
vbausedrange009
⑪写真⑨のシートに対して付加コードを実行
vbausedrange010
⑫検証コードの実行後

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

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

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

付加コードE
Sub UsedRange付加コードE()
 Range("G15").ColumnWidth = 11
End Sub
vbausedrange013
⑬写真⑪のシートに対して付加コードを実行
vbausedrange014
⑭検証コードの実行後

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

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

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

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

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

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

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

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

vbausedrange014
写真⑧
  •  セル幅を戻す
  •  セル高さを戻す
  •  セルの背景色を戻す

トライアル1

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

「結果は変化なし」

vbausedrange015
⑮写真⑭の状態から付加コードを実行
vbausedrange016
⑯指定範囲的には変化していません

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

トライアル2

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

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

⑰写真⑭の状態から付加コードを実行 見た目は写真⑮と同じ
⑱検証コード実行すると、行列幅変更による範囲指定は解除されたことが分かる

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

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

トライアル3

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

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

⑲写真⑭の状態から付加コードを実行 見た目は写真⑮と同じ
⑳検証コード実行すると、行列幅変更による範囲指定の解除に加えセルの背景色の加工の影響も解除された

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

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

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

vbausedrange006
写真⑥

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

HTML
Sub UsedRange付加コードI()
 ActiveSheet.UsedRange.Borders.LineStyle = xlLineStyleNone
End Sub
21写真⑳の状態から付加コードを実行 罫線エリア内のデータに設定された書式設定は保存されている
22検証コード実行すると、罫線を消去したにもかかわらず、指定範囲に変化はない。

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

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

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

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

検証コード2
Sub UsedRange検証コード2()
 ActiveSheet.UsedRange.Resize(8).Select
End Sub
vbausedrange023
写真22
23検証コード2を実行しリサイズした

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

vbausedrange002
写真②

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

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

<strong>付加コードJ</strong>
Sub UsedRange付加コードJ()
 With ActiveSheet.Cells
     .UseStandardWidth = True
     .UseStandardHeight = True
     .Clear
 End With
End Sub
24写真23に付加コードを実行
25検証コードを実行すると初期状態を確認

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

範囲設定の検証結果

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

  • データの挿入
  • 書式の設定
  • サイズの変更

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

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

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

可逆性の検証結果

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

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

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

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

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

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

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

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

①Addressプロパティ

<strong>Addressコード</strong>
Sub UsedRangeAddressコード()
 Dim Ans As Variant
  ActiveSheet.UsedRange.Select
  Ans = ActiveSheet.UsedRange.Address
  MsgBox Ans
End Sub
vbausedrange027

②Borderプロパティ

<strong>Borderコード</strong>
Sub UsedRangeBorderコード()
 ActiveSheet.UsedRange.Select
 ActiveSheet.UsedRange.Borders.LineStyle = xlContinuous
End Sub
vbausedrange028

③ColumnWidthプロパティ

<strong>ColumnWidthコード</strong>
Sub UsedRangeColumnWidthコード()
 ActiveSheet.UsedRange.Select
 ActiveSheet.UsedRange.ColumnWidth = 10
End Sub
vbausedrange029

④・⑬Count、Rowsプロパティ

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

⑤・⑦EntireRow、Hiddenプロパティ

<strong>EntireRowHiddenコード</strong>
Sub UsedRangeEntireRowHiddenコード()
 Dim Ans As Variant
  ActiveSheet.UsedRange.Select
  Ans = ActiveSheet.UsedRange.EntireRow.Hidden
  MsgBox Ans
End Sub
vbausedrange031

⑥Heightプロパティ

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

⑧Interiorプロパティ

<strong>Interiorコード</strong>
Sub UsedRangeInteriorコード()
 ActiveSheet.UsedRange.Select
 ActiveSheet.UsedRange.Interior.ColorIndex = 3
End Sub
vbausedrange033

⑨NumberFormatLocalプロパティ

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

⑩Offsetプロパティ

<strong>Offsetコード</strong>
Sub UsedRangeOffsetコード()
 ActiveSheet.UsedRange.Select
 ActiveSheet.UsedRange.Offset(9, 1).Select
End Sub
vbausedrange035

⑪Resizeプロパティ

<strong>Resizeコード</strong>
Sub UsedRangeResizeコード()
 ActiveSheet.UsedRange.Select
 ActiveSheet.UsedRange.Resize(6).Select
End Sub
vbausedrange036

⑫RowHeightプロパティ

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

⑭Valueプロパティ

<strong>Valueコード</strong>
Sub UsedRangeValueコード()
 ActiveSheet.UsedRange.Select
 ActiveSheet.UsedRange.Value = 555
End Sub
vbausedrange038

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

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

①Clearメソッド

<strong>Clesrメソッド</strong>
Sub UsedRangeClesrメソッド()
 ActiveSheet.UsedRange.Clear
  ActiveSheet.UsedRange.Select
End Sub
vbausedrange039
注意

 このように指定範囲がセルA1にならない時は、セルサイズの編集履歴が残っているためです。専用のセルサイスをデフォルトにするコードを先に実行させることが必要です。

②Copyメソッド

<strong>Copyコード</strong>
Sub UsedRangeCopyコード()
 ActiveSheet.UsedRange.Copy Range("D11")
 ActiveSheet.UsedRange.Select
End Sub
vbausedrange040

③Deleteメソッド

<strong>Deleteコード</strong>
Sub UsedRangeDeleteコード()
 ActiveSheet.UsedRange.Delete
 ActiveSheet.UsedRange.Select
End Sub
vbausedrange041

④Insertメソッド

<strong>Insertコード</strong>
Sub UsedRangeInsertコード()
 ActiveSheet.UsedRange.Insert Shift:=xlDown
 ActiveSheet.UsedRange.Select
End Sub
vbausedrange042

⑤Selectメソッド

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

vbaselacteyecatch VBA 似た者SelectとActivateの違いを理解

まとめ

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

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

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

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

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

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

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

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

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

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

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