VBAで名前の定義をするといろいろ楽
セル範囲の名前の定義をVBAでやったら便利
転記するごとにセル範囲を定義し直す
このときみたいなデータ転記系の処理をした場合、ワークシート関数のCOUNTIFなんかを使って種別ごとの数を勘定したい、ということがよくある。
しかしながら、転記件数が変化する場合、COUNTIFの第1引数(範囲)が変化することになる。
だからといって、毎回転記処理が終わってから手動でCOUNTIFをセットするというのもマヌケな話。
そこで、
抽出するごとにCOUNTIFの第1引数になるセル範囲に名前を定義すりゃいいじゃん!
と考えた。
方針
手順は次の通り。
- まず、対象となるセル範囲に「StyleRangeForCount」と名前を付けておく
- VBAで、一旦「StyleRangeForCount」と名付けられたセル範囲のNameプロパティをDeleteする
- 新たにCOUNTIFの第1引数にしたいセル範囲を取得する
- 3.で取得したセル範囲のNameプロパティに「StyleRangeForCount」という名前をセットする
このようにしておいて、COUNTIFの第1引数を「StyleRangeForCount」にしておけば、転記結果が変化してもCOUNTIFの第1引数が変化に追随してくれるはずだ。
リスト1
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の第1引数にすべき範囲を変数objRangeにセット。
最後に、(3)の
objRange.name = "StyleRangeForCount"
で新たにできたCOUNTIFの第1引数にすべき範囲に「StyleRangeForCount」と名前を付けている。
実行
ちょっと分かりにくいかも知れないが、最初は、D3~D37のセル範囲に「StyleRangeForCount」という名前が定義されている。
転記元データをちょっといじくって、失格選手(w)を増やし、再度転記処理を行ってみる。
当然、転記される件数が減るので、こんな状態になる。依然、「StyleRangeForCount」と名付けられたセル範囲はD3~D37のまま。
ここで、リスト1を実行。
セル範囲D3~D37から名前がぬぐい去られていることがお分かりだろうか。
んで、セルの選択範囲をD3~D27に変えると、
ほれ、「StyleRangeForCount」という名前が定義されている。
こんなふうに、COUNTIFの第1引数を「StyleRangeForCount」にしておくと、
失格者を増やす前
失格者を増やした後
と、COUNTIFの結果が転記結果に追随していることが分かる。
感想
手作業で名前の定義や編集・削除を行うのはかなりメンドクサイんだけど、VBAでやると簡単・便利だと思いました。