VBAで名前の定義をするといろいろ楽

セル範囲の名前の定義をVBAでやったら便利

転記するごとにセル範囲を定義し直す

このときみたいなデータ転記系の処理をした場合、ワークシート関数のCOUNTIFなんかを使って種別ごとの数を勘定したい、ということがよくある。

しかしながら、転記件数が変化する場合、COUNTIFの第１引数（範囲）が変化することになる。

だからといって、毎回転記処理が終わってから手動でCOUNTIFをセットするというのもマヌケな話。

そこで、

抽出するごとにCOUNTIFの第１引数になるセル範囲に名前を定義すりゃいいじゃん！

と考えた。

方針

手順は次の通り。

1. まず、対象となるセル範囲に「StyleRangeForCount」と名前を付けておく
2. VBAで、一旦「StyleRangeForCount」と名付けられたセル範囲のNameプロパティをDeleteする
3. 新たにCOUNTIFの第１引数にしたいセル範囲を取得する
4. 3.で取得したセル範囲のNameプロパティに「StyleRangeForCount」という名前をセットする

このようにしておいて、COUNTIFの第１引数を「StyleRangeForCount」にしておけば、転記結果が変化してもCOUNTIFの第１引数が変化に追随してくれるはずだ。

リスト１

Public Sub renameRange()
  Set styleSh = ThisWorkbook.Worksheets("戦法別")
  Dim objRange As Range
  Dim lastRow As Integer
  Range("StyleRangeForCount").name.Delete    '……(1)'
  With styleSh
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set objRange = Range(.Range("D3"), _
                         .Range("D" & lastRow))    '……(2)'
  End With
  objRange.name = "StyleRangeForCount"    '……(3)'
End Sub

実際には、もう少し複雑なコードを書いたんだが、説明のために単純化したコードを載っけている。

まず、(1)の

Range("StyleRangeForCount").Name.Delete

で、もともとの「StyleRangeForCount」という名前をDelete。

次に、(2)の

Set objRange = Range(.Range("D3"), .Range("D" & lastRow))

で、新たにできたCOUNTIFの第１引数にすべき範囲を変数objRangeにセット。

最後に、(3)の

objRange.name = "StyleRangeForCount"

で新たにできたCOUNTIFの第１引数にすべき範囲に「StyleRangeForCount」と名前を付けている。

実行

ちょっと分かりにくいかも知れないが、最初は、Ｄ３～Ｄ37のセル範囲に「StyleRangeForCount」という名前が定義されている。

転記元データをちょっといじくって、失格選手（ｗ）を増やし、再度転記処理を行ってみる。

当然、転記される件数が減るので、こんな状態になる。依然、「StyleRangeForCount」と名付けられたセル範囲はＤ３～Ｄ37のまま。

ここで、リスト１を実行。

セル範囲Ｄ３～Ｄ37から名前がぬぐい去られていることがお分かりだろうか。

んで、セルの選択範囲をＤ３～Ｄ27に変えると、

ほれ、「StyleRangeForCount」という名前が定義されている。

こんなふうに、COUNTIFの第１引数を「StyleRangeForCount」にしておくと、

失格者を増やす前

失格者を増やした後

と、COUNTIFの結果が転記結果に追随していることが分かる。

感想

手作業で名前の定義や編集・削除を行うのはかなりメンドクサイんだけど、VBAでやると簡単・便利だと思いました。