カテゴリー: セル操作

'ABC列で部分一致の検索置換を実行する
Range("A:C").Replace What:="検索文字", Replacement:="置換文字", LookAt:=xlPart

'ABC列で完全一致の検索置換を実行する
Range("A:C").Replace What:="検索文字", Replacement:="置換文字", LookAt:=xlWhole

セル内文字列の置き換えには「Replace」メソッドを使用します
Excelの標準機能と同じように、セルに入力された内容と条件一致するものを検索して、見つかった場合に置き換えを実行する際に使用します

引数の「What」が検索対象の文字列です
置き換えする文字列は「Replacement」に指定します
この2つの指定により、任意の文字列を検索して置き換えを実行することができます

引数「LookAt」の設定で部分一致か完全一致かの選択ができます
部分一致の場合は「xlPart」を設定し、完全一致の場合は「xlWhole」と設定します
検索処理でも同じ事を言えますが引数の設定は前回実行した設定が引き継がれます
なので引数の設定は必要のない部分でも一応設定しておくほうが安心です
部分一致と完全一致が意図せず入れ替わっていたりします

完全一致とすれば、セルの中にある文字列全体が検索と全く同じ時のみ置換の対象になります
部分一致にすれば、セルの中にある文字列の一部だけを置換することが出来ますが、複数当てはまる場合は全て置換対象になる点だけ注意してください
あまりにも短い文字を検索対象にしていると、意図しない変換が行われてしまいます

'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に移動させてから実行したとしても範囲が移動することはありません

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

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

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