カテゴリー: セル操作

'2行目以降を削除
Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

'2行目以降を消去
Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Clear

関数などで参照しているセル範囲である場合にはこちらを利用してください

データ集計を行ったり、フィルタデータの貼り付けなどで元々あるデータは削除してデータを貼り付けする処理はよくあります
この際に見出し行は削除したくない場合があります
そんな場合に使用するコードです

このコードを使用すると2行目以降のすべてのセルを削除しますが、1行目はそのままなので見出しを再度作成したりする必要が無くなります

ちなみに画像ではテーブル機能のデータ範囲以下全てを削除しています
テーブル機能の場合はデータが無くても、データ範囲1行目には背景色の設定が表示されますので、実際にはちゃんと削除されています

ですがこの選択範囲はデータ行のみになるので、もし未保存の削除セルがあった場合LastCellなどでうまく最終セルが取得できない場合があったり、コピー元のセル範囲がそういったセルを作成してしまっていたりすることがあるので、なるべく例コードの様にデータ範囲を初期化する場合は全てのセル範囲を指定する方が無難です

コード解説

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

このコードは1行ステートメントですが、複数のプロパティを使用しているのでプロパティごとに分割して解説を行います

Range("A2").Select

そのRangeオブジェクトの1つ目の引数はこれまたRangeオブジェクトとなっています
その引数にはA2が指定されています
なのでこの引数でSelectメソッドを実行すると画像のセルが選択されます
ここが始点のセルになるということですね
なのでここのA2を変更することで任意の行数に変更することが出来るということになります
A3にすれば3行目以降全てが削除対象となり、1・2行目はそのまま保持される事になります

Range("A" & Cells.Rows.Count).Select

次の2つ目の引数にもRangeオブジェクトが指定されていますが、今度は単純にセルAddressが入力されているわけではありません

「Cells.Rows.Count」というのは、このシートに存在するセル範囲の行数を数えています
その数字の前にAが入力されているので、A列の最終行が指定されます
画像の通りで行数値は「1048576」となりA1048576セルが選択状態になっています
ここが終点セルとなります、要は以降全部ってことですね

Range(Range("A2"), Range("A" & Cells.Rows.Count)).Select

この2つを始点と終点としてセル範囲指定をすることで、A2からA1048576までが範囲選択された状態となります
画像の様に2行目以降がすべて選択されていることが確認できます

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Select

ここまでの対象範囲の取得ではA列だけになってしまいます
そこでこの対象範囲を最終列まで広げていきます

EntireRowプロパティは、A列だけの範囲を列方向に広げてくれます
これを使用することで画像の通りに最終列まで範囲を広げることが出来ます

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

最後にDeleteメソッドを使用して、その範囲を削除します
上記までで指定行範囲を取得できているので、そこに対して削除を実行します

この範囲を別のシートで関数等で参照している場合、セル参照がエラー値（#REF!）になってしまいます
その場合はDeleteメソッドではなくClearメソッドを使用することでセルの初期化を行うことが出来ます

それが例コードの2つ目のものです
単純にメソッドをClearメソッドに変更しただけですが、これを行うと書式含めすべてが初期化されるので、見た目的には削除とほぼ同等の効果です

上記に記載していますが、このメソッドではセルが削除されないため、関数などの参照を行っている場合に自動的に範囲が修正されたり「#REF!」エラーになることを防ぐことが出来ます

作り込みの凄まじい関数ファイルなどで処理を行う場合はこちらを利用しておいた方が無難です
なんせ作り込まれた関数ファイルはVBAより遥かに難解ですので

なおブックのサイズにもよりますが、こういった削除処理を行った際は処理最後にでもブックの上書き保存を行っておいた方が良いです

なぜなら、削除したセル範囲のLastCellなどはそこでしか更新されないためです
またこれを行うことで、スクロールバーの可動域も新しいデータ範囲に調整されますので、あまりにも削除前とのデータ範囲に差がある場合にはユーザーにとっても意味のある保存と言えます

ただ保存はサーバー上であったり、ブックサイズが大きいと時間がかかってしまい
待ち時間がユーザーにとってのストレスになることもあることは認識しておきましょう

'指定範囲内のみを対象とする
Range("A1:C3").SpecialCells(xlCellTypeConstants).ClearContents

'すべての使用済みセル範囲を対象とする
Cells.SpecialCells(xlCellTypeConstants).ClearContents

セル範囲の中で値の入力されたセルのみをクリアするには「SpecialCells」メソッドの引数「xlCellTypeConstants」を使用します

すでに関数によってある程度フォーマットが完成したシートに対して、入力値の部分だけを初期化する場合に利用します
単純にセル範囲全てをクリアしてしまうと、関数も消去されてしまうためです

コード解説

画像の動きを確認してください
前提としてC列はA列＋B列の数式が入力されています
なので値入力された範囲はA2～B7となります

主にすでにあるフォーマットから入力範囲のデータを消去する場合に利用する場面が多いため記事コードでも前者の方が利用頻度は高いと思われます

'指定範囲内のみを対象とする
Range("A1:C3").SpecialCells(xlCellTypeConstants).ClearContents

このコードではA1～C3という範囲指定を行った上で、値のセルに対してClearContentsメソッドを実行して入力値を消去しています
画像ではA2～B3がその範囲に該当するため、そこが消去されています

こうして指定することでせっかく作った関数を消去せずに入力フォーマットを初期化できます

'すべての使用済みセル範囲を対象とする
Cells.SpecialCells(xlCellTypeConstants).ClearContents

こちらではシート全体のうち、使用済みセル範囲であるUsedRangeプロパティで取得される範囲の全ての値セルがクリアされます
この方法だと見出しや題目等の部分まで消去されるので、あまり利用頻度は無いかもしれません

ちなみにセル内に「=”見出し名”」というような入力を行えば、そのセルは数式と認定されるようになるので全ての範囲から値セルを消去しても問題なくなります
ただ全てを振り替えたり、修正があった場合に非常に面倒なので現実的な利用方法ではありません

この方法でセル範囲を限定すれば、ClearContentsメソッドを他のものに変更するだけで、値セルにだけ背景色を設定したりすることも簡単に行うことが出来ますので、アイデア次第で広がる汎用性のあるものだと思います
ただただUsedRangeに引っ張られる点を除けば、ですが・・・

Debug.Print Cells.SpecialCells(xlCellTypeFormulas).Address

数式が入力されたセルを取得するには「SpecialCells」メソッドに引数「xlCellTypeFormulas」を使用します
この引数設定では数式が入力されたセル範囲を取得できます

このメソッドには利用時に注意点があります
それはUsedRangeプロパティが基準とされる点です
コードの解説とともに解説します

コード解説

記事コード含め合計4行を実行しています
前提として、シートの選択範囲が数式が入ったセルです
それ以外は空白セルは空白、数値が入っている部分とC列が数式です

記事コードが実行されると選択範囲と同じ範囲が取得されています
C2～C7のセル範囲が取得されます

ここで1つ注意したいのが、Cellsオブジェクトはワークシート全体のセル全てを指定しますが、内部的には自動的にUsedRangeプロパティに変換されます
ただこの引数設定ではあまり影響は無いかもしれません

ActiveCell.SpecialCells(xlCellTypeFormulas).Address

Range("A1").SpecialCells(xlCellTypeFormulas).Address

→→→　$C$2:$C$7

これら2行の指定でも同じくC列の入力範囲が取得されています
指定Rangeオブジェクトが単一の場合にはCellsオブジェクトと同様にUsedRangeプロパティに変換されて取得されます

ほとんどそういった利用は無いとは思いますが、数式かどうかの判定には利用できないですね

Range("A1:C3").SpecialCells(xlCellTypeFormulas).Address

→→→　$C$2:$C$3

ここではRangeオブジェクトで範囲指定を行っています
この場合はその範囲内で条件を満たすものが取得されます

A1～C3までのセル範囲のうち数式が入力されたセルはC2とC3となるため、その範囲が取得されています

この様に全ての範囲から取得を行うのではなく、限定された範囲から取得する場合はその範囲を指定してあげれば良いだけです

ただ今回の引数の際にはあまり影響は無いのですが、やはりUsedRangeプロパティの影響を受けています
指定範囲内であってもUsedRangeプロパティ外は検索対象に入りません

つまりIntersectメソッドを使ったときの様に、指定範囲とUsedRangeプロパティの範囲の重複する部分のみが対象となってしまいます

'セルが数式かどうかを判定する（Trueなら数式）
Debug.Print Range("A1").HasFormula

セル内容が数式かどうかを判定するには「HasFormula」プロパティを使用します
Trueならセル内容は数式が入力されています、値等の数式以外であればFalseが返されます

セルの入力値には大きく分けて数式と値が存在しますが、それを判定するためのプロパティです
これはIs～～関数等のものではなく、Rangeオブジェクトのプロパティである点に認識が混同しないように注意が必要です

ちなみに数式と関数はこのプロパティ上は同一で判断します

コード解説

画像の動きを確認してください

まず前提としてB1セルにはA1セルに表示された数式が入力されています
ただ空白を返しているだけの数式になります
B2セルには何も入力されていません

なので空白かどうかの判定に関しては出力されるのは「True」が出力されています

これは空白チェック時の要注意点の1つなのですが、数式の結果が空白を返している場合は、セルが空白と混同して判定されてしまいます
この場合は空白かどうかを判定するだけでは不十分です

Debug.Print Range("B1").HasFormula

→→→　True

次にプロパティを利用した判定を取得してみます
この場合はB1には数式が入力されているため、Trueが返されます

ここでさらに空白チェックを複合的に条件を立てることにより、数式で空白が返されたものは除外することが出来ます

Debug.Print Range("B2").HasFormula

→→→　False

B2に関しては何も入力されていないため数式では無いのでFalseが返されます
ここは数式でなければ入力値があったとしても値であればFalseとなります

このプロパティを利用することで、指定セルから数式の空白を含めた空白を探すような特殊な場面で活用できます

'A10000セルを見える位置に移動
Range("A10000").Show

処理を終了させた段階で指定の個所を見える様にしておきたい場合があります
そんなときに使用するのが「Show」メソッドです

このメソッドを使用すると、指定したセルがウィンドウ内に収まるように自動的にスクロールされます
また、その際に指定したセルは選択状態にはなりません

選択セルは変更したくないけど、画面を移動させたい場合に有効です

ただ、画面内のどこかまでが指定できません
しかも指定セルが画面内にあれば移動しない場合もありますので少し注意が必要です

コードの動き

実際にコードを実行してみると、動きがわかりやすいです

画像の動きを確認してください
A10000セルを表示状態にすると、真ん中に表示されるように画面がスクロールされています
そしてその際、選択セルがA1のままで選択範囲が移動していないことも確認できます

そして、このメソッドの最大の注意点が、画面更新を抑止している時には無効になる点です
画面更新を抑止した状態でA1セルを対象に実行しても変化がありません
抑止を解除してから、再度実行するとちゃんと画面が移動します

処理後に画面を移動させたい場合は多いので、あまり問題にならないとは思いますが画面更新を抑止する処理を作成した場合は解除してから実行するようにしてください

'選択範囲が結合セルかを調べる
Debug.Print Selection.MergeCells

'シート内に結合セルがあるかを調べる（Nullなら存在有）
Debug.Print Cells.MergeCells

指定したセルが結合セルかどうかを調べるには「MergeCells」プロパティを使用します
このプロパティの返し値によって判定を行います

指定したセルが結合されていれば「True」、結合されていなければ「False」をそれぞれ返します
さらに、その両方が含まれていれば「Null」が返されます

コード解説

この画像の前提として、A2とB2が結合されたセルです

Debug.Print Selection.MergeCells

結合されたセルが選択状態になっていますので、このコードを実行するとTrueが返されます
つまり、選択している範囲は結合されて1セルとなってい状態と判定できます

このプロパティは、単体での判定でしかTrueを返しません
例え指定範囲が全て結合されたセルだとしてもNullが返されてしまいます

逆にすべてが結合されていないセルであればFalseが返されます

そしてその仕様から、シート全体などの範囲で使用することで結合セルの存在を調べられます

Debug.Print Cells.MergeCells

このコードを実行するとNullが返されます
結合セルとされていないセルが範囲内に含まれているからです

画像の最後の動きの部分を確認してください
再度取得する前に、すべての結合を解除しています
その状態でプロパティを取得させると、Falseが返されます
つまり、シート内に結合セルが存在しないことを意味します

どのセルが、という所まではこのプロパティで調べられませんが、存在するかどうかは調べられます

基本的に結合セルとは、見栄えなどの書式的な使い方が多く、VBA泣かせのセルです
あったら何とか解除してしまえないかと思ってしまいますが、この判定や解除をうまく活用して乗り越えてください

'選択範囲内セルの結合を解除
Selection.UnMerge

'選択範囲内セルの結合を解除
Selection.MergeCells = False

結合されたセルの結合を解除するには「UnMerge」メソッドを使用します
このメソッドは指定した範囲内にある結合セル全てを一括で解除します

また、本来は結合セルの存在確認を行うMergeCellsプロパティにFalseを代入することでも実行することが出来ます

コード実行時の動き

画像の確認をしてください
前提として、2行目はすでに結合済みのセルです
結合解除を行った際に、そちらは解除されていないことを確認してください

Selection.UnMerge

選択範囲内にあるすべての結合セルの結合を解除します
指定範囲を限定することで、シート内に複数の結合セルがあったとしても限定して解除することが可能です

Cells.UnMerge

なので、逆に指定範囲を広げてシート全てのセル範囲を指定するとシート内にあるすべての結合セルの結合を解除することが出来ます
書式統一のために使用された結合セルが多いので、あまりこういった使い方はしないかもしれませんが、結合セルがシート内に1つしか無いのであれば、そのセル範囲を検索する必要がなく楽に解除することが出来ます

MergeCellsプロパティに関しては別の記事で解説していますので、ここでは割愛します

'選択範囲の結合
Selection.Merge

'選択範囲の横方向への結合
Selection.Merge True

セル範囲を結合するには「Merge」メソッドを使用します
このコードを使用すると指定したセル範囲が結合セルとなります

またメソッドの引数に「True」を指定することで、横方向のみの結合を行うことが出来ます

Selection.MergeCells = True

本来は結合セルの存在確認に使用するMergeCellsプロパティを使用することでも、例コードの1つ目と同じ動きの結合を行うことが出来ます

コードの動きの解説

各結合の後にある「Cells.UnMerge」は結合セルを解除するコードです
結合の動きを確認するために書いています

Selection.Merge

これを実行すると、選択範囲が1つのセルに結合されます
行列ともに1つのセルとして結合されます

Selection.Merge True

このメソッドの引数にTrueを指定することで横方向のみの結合を行うことが出来ます
画像の動きを確認してください
選択範囲が複数行である場合に、横にのみ結合が実行され、縦方向には結合が実行されていないことが確認できます

これは、通常操作の「横方向に結合」と同じ動きをします
複数行で縦には結合しない場合に一括で指定することが出来る方法です

1つ目では省略されていましたが、この引数を省略するとFalseが指定されていることになります

Selection.MergeCells = True

このコードは、通常は選択範囲に結合セルが存在するかを調べて、存在すればTrueを返すプロパティですが、Trueを代入することで例コードの1つ目と同じ動きを実行できます

ただ、このコードでは横方向の指定が出来ません
ですが、解除する場合も使用でき、Falseを代入するだけで可能です

結合するにはTrueを、解除するにはFalseを代入するというのがイメージしやすいので使い勝手はいいと思います
蛇足ではありますが便利なので紹介します

コード実行時の注意点として、指定範囲に複数のデータが存在する場合はExcelの警告が発生します

結合セルを使用したことがある人なら見たことあると思いますが、複数のデータがある場合にデータの欠損が発生するため警告が表示されます
ここでキャンセルを選択すると、実行時エラーが発生します

動的にセル結合を行う場合にはこの点のみ注意が必要です

'A1から1行3列の範囲を選択
Range("A1").Resize(1, 3).Select

セル範囲サイズを減らしたり増やしたりする場合には「Resize」プロパティを使用します

このプロパティを使用すると、指定セル範囲の左上のセルから指定した行・列数のセル範囲に変更できます

例コードではA1セルを起点にセル範囲を変更しています
プロパティの引数1つ目が行数、2つめが列数になりますので、ここでは1行3列を指定することになります

つまり、「A1,B1,C1」の3つのセルが選択状態になります

使用時の動き

実際にコードを実行したときの動きの画像です
この動きを元に解説を行います

最初にA1セルを選択状態にしています

次に記事コードを実行します
例コードと同じものを実行してますので、解説した通りA1~C1セルが選択状態になります

言うなればこのコードは「Range(“A1:C1”).Select」と同じ動きをすることになります

そして上記でも解説したように、このプロパティで指定される起点となるのはセル範囲の左上のセルです
そのため、アクティブセルをC1に移動させてから実行したとしても範囲が移動することはありません

また、その際にセル範囲選択が再設定されるためアクティブセルは左上に移動してしまいます
この点については、アクティブセルを参照する様な処理作成を行っている場合には注意が必要です

このプロパティの大きな使い道は、特定の列だけに選択をさせたり、データ表範囲の見出しだけを除いて選択状態にする様な場合に有効なプロパティです

またこのプロパティを有効に使用すれば、配列データを出力する際の範囲選択も簡単に行うことが出来ます

Debug.Print Range("A1")

セルに入力されたデータを取得する際には通常上記のコードで対応します
このコードを実行すると、アクティブシートのA1セルの内容がイミディエイトに出力されます

A1セルに「100」と入力されていれば、出力されるのも「100」です
これは当然の話ですが、このコードには実際にはセルの入力値を指定するプロパティが省略されています

Debug.Print Range("A1").Value

省略しない形の場合は、このコードの様になります
省略した場合の既定のプロパティは「Value」プロパティです

これは入力されている値を取得するプロパティです
通常値の取得には、なんの問題もありませんので処理中ではほぼ省略する形で作成することが多いプロパティです

ただ、セルにあるデータをイメージしてください
セルには数値や文字列以外にも入力されるものがあります、日付やエラー値、関数などです
このあたりを取得したい場合にはValueプロパティでは対応できなくなります
その場合にはそれぞれ別々のプロパティがあります

取得情報によって切り替えるプロパティ

このプロパティを説明するうえで一番分かり易い例が、関数で求められた日付データです
この画像のA2セルを対象に取得を行ってみます

先にこの画像を確認してください
これらの他にも取得プロパティは有りますが、とりあえずこれだけあればおおよそ問題ありません

Debug.Print Range("A2")
Debug.Print Range("A2").Value

→→→　#2020/05/19#

これはすでに解説したようにValueプロパティが省略されているので値が取得されます
なので、この2行のコードは同じものを意味しています
日付データを取得しているので、日付の両側に#が付与されています

Debug.Print Range("A2").Text

→→→　"2020年5月19日"

セルに表示されている状態のデータを取得するには、「Text」プロパティを使用します
これは基本的には、表示形式が適用された後のデータを取得します
Textなので、例え数値であっても文字列として取得されます

そして、このプロパティは「表示されている状態」の、と言ったように画面に見えている状態を取得します

例えば、入力値がセルの列幅より大きく表示できない場合Excelでは「#」の連続文字列で表現されることがありますが、それもしっかりその文字列を取得します

この画像の様に「#」がイミディエイトに出力されます
実際は上記の内容と全く同じデータで「2020年5月19日」が入力されていますが、列幅を狭めた結果、表示できない表現として画像の様になります

Textプロパティはこの表現さえも素直に取得を行います
画面に表示されている状態というのは、こういうことになります

Textプロパティは表示形式が適用されたデータではない、という点に注意が必要です

ちなみに、この内容を反対に応用すれば入力したデータが列幅に収まっているかどうかを判定することも出来ますね

Debug.Print Range("A2").Formula

→→→　=A1+B1

数式を取得する場合は「Formula」プロパティを使用します
このプロパティであれば、関数の結果の値ではなく数式自体を取得することが可能です

VBAでセルに入力された数式を取得して作業を行うことは少ないのですが、配列への代入時などでは重宝するプロパティです

また、このプロパティはセル入力値が数値や文字列であれば、それをそのまま取得するので数式以外が取得できない、というものではありません

Debug.Print Range("A2").Value2

→→→　43970

日付データが実際には内部数値で扱われている、という点はある程度Excelを勉強した方ならご存じかとは思います
その内部数値を取得することが出来るのが「Value2」プロパティです

このプロパティは、なんの加工も行われていない純粋なデータを取得するプロパティです
リファレンスでも書いてあることなのですが、その性質上セルのプロパティで処理速度が最高のプロパティです

処理速度は間違いなく速いです、そこでValueプロパティと比較してみました
リファレンスにもあり、結果が明らかですのでコードは記載しませんが、内容としては変数にセルの入力値を代入させるだけの処理をループ処理で実行しました

自分の環境になりますが、100万セルで0.1秒ほどの差でした
Value2プロパティの方がやはり速かったです

ですが、100万セルで0.1秒ほどなので相当量のビッグデータを扱う際に気にしてみる
程度で十分な差でしかありませんでした

ちなみに、これも明らかですがTextプロパティは圧倒的に遅かったです
これは文字列を扱っているので想定の範囲内ではあります
まあ、Textプロパティは目的があって使用する場面の方が多いのであまり気にしなくても良いかなとは思います

処理速度の優位性はありますが、内部数値が欲しい、という場面でValue2プロパティを使用すれば良いです