VBA UsedRangeプロパティの指定範囲と関連プロパティ

vbausedrangeeyecatch

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

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

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

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

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

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

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

UsedRangeプロパティを使う

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

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

UsedRangeの記述方法

構文

  •  Worksheetオブジェクト.UsedRange

Worksheetオブジェクト

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

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

UsedRangeの指定範囲を検証する

UsedRangeが指定する範囲は、

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  •  セル幅を戻す
  •  セル高さを戻す
  •  セルの背景色を戻す

トライアル1

付加コードF
Sub UsedRange付加コードF()
 Range("G15").ColumnWidth = 8.38
 Range("A13").RowHeight = 13.5
 Range("F12").Interior.ColorIndex = False
End Sub
⑮写真⑭の状態から付加コードを実行
⑯指定範囲的には変化していません

付加コード実行の結果、シートの見た目はセル加工が戻され元に戻ったように見えますが、「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コードを使うことが必要ということが判明しました。

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

写真⑥の状態にまで戻すには、付加コード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を実行しリサイズした

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

エクセルシートを写真②の初期状態にまで戻すのは簡単です。注意すべき点は、「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

②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

④・⑬Count、Rowsプロパティ

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

⑤・⑦EntireRow、Hiddenプロパティ

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

⑥Heightプロパティ

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

⑧Interiorプロパティ

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

⑨NumberFormatLocalプロパティ

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

⑩Offsetプロパティ

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

⑪Resizeプロパティ

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

⑫RowHeightプロパティ

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

⑭Valueプロパティ

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

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

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

①Clearメソッド

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

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

②Copyメソッド

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

③Deleteメソッド

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

④Insertメソッド

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

⑤Selectメソッド

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

まとめ

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

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

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

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

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

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
vbastudyeyecatch002 エクセルVBAを独学で習得する!ために大切な7つのポイントを解説します

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできます。
Udemy1eyecatch エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

 

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

 

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