VBAでCurrentRegionを活用する
ツイッターで見かけて書きたくなったので許可を得て書いてみます。
このブログにあるように CurrentRegion で範囲全体を指定してあげると、その範囲がまるっと移動したとき（左上のセルがB2になったときとか）でも修正がとても楽ですし、最終行の取得も cr.Rows.Count とスッキリ書けてとてもいいですよね。
でもまあ合計したりカウントしたりするのはDictionaryが楽ですので、それで書き直してみました。
Sub hoge1()
Dim cr As Range: Set cr = Range("a1").CurrentRegion
Dim i As Long, sum As New Dictionary, cnt As New Dictionary, Key
For i = 2 To cr.Rows.Count
sum(cr(i, 1).Value) = sum(cr(i, 1).Value) + cr(i, 3).Value
cnt(cr(i, 1).Value) = cnt(cr(i, 1).Value) + 1
Next
For Each Key In sum.Keys
Debug.Print "sum", Key, sum(Key)
Debug.Print "cnt", Key, cnt(Key)
Debug.Print "------------------------"
Next
End Sub＜結果＞
sum 1 10000
cnt 1 4
------------------------
sum 2 26000
cnt 2 4
------------------------
sum 3 42000
cnt 3 4
------------------------いいですね、とてもスッキリ。
ちなみに、Dictionaryはどんな型も受け入れてくれる心の広い人なので、cr(i,1).Value と書く必要があります。省略しちゃうとRange型として格納してくれます。お節介すぎるくらい優しい人ですね、そういう人は嫌いじゃありません。で、僕はいつも忘れて軽くハマります、てへ。配列にしちゃうことが多いかもしれません。
上記ブログでは預金額が１以上のもので、かつ3000円以外のものを集計したかったようですね。僕ならこう書きます。
Sub hoge2()
Dim cr As Range: Set cr = Range("a1").CurrentRegion
Dim i As Long, sum As New Dictionary, cnt As New Dictionary, Key
For i = 2 To cr.Rows.Count
'メンテナンスのしやすさを重視してあえてGoToで処理します'
If cr(i, 3).Value < 1 Then GoTo continue:
If cr(i, 3).Value = 3000 Then GoTo continue:
'あとはラベル以外全部一緒です、インデントが変わらないのが嬉しいですね'
sum(cr(i, 1).Value) = sum(cr(i, 1).Value) + cr(i, 3).Value
cnt(cr(i, 1).Value) = cnt(cr(i, 1).Value) + 1
continue:
Next
For Each Key In sum.Keys
Debug.Print "sum", Key, sum(Key)
Debug.Print "cnt", Key, cnt(Key)
Debug.Print "------------------------"
Next
End Sub＜結果＞
sum 1 7000
cnt 1 3
------------------------
sum 2 26000
cnt 2 4
------------------------
sum 3 42000
cnt 3 4
------------------------きちんと3000円を除外できていますね。条件を増やしたかったら同様のIF文を増やしてあげるだけで済みますので便利です。
GoToだって節度をわきまえて使ったらいいコードになりますので毛嫌いせずに試しにしばらく使ってみるといいんじゃないでしょうか？
さて、ツイ主さん、どうやらDictionaryではなくRangeで処理したかったそうです。よし、やってみましょう。僕ならRange型の配列変数にしたくなりますね。こんな感じでしょうか。
Sub hoge3()
Dim cr As Range: Set cr = Range("a1").CurrentRegion
Dim i As Long, trgRange() As Range
ReDim trgRange(1 To 3)
For i = 2 To cr.Rows.Count
If trgRange(cr(i, 1)) Is Nothing Then
Set trgRange(cr(i, 1)) = cr(i, 3)
Else
Set trgRange(cr(i, 1)) = Union(trgRange(cr(i, 1)), cr(i, 3))
End If
Next
For i = LBound(trgRange) To UBound(trgRange)
Debug.Print "sum", i, WorksheetFunction.Sum(trgRange(i))
Debug.Print "cnt", i, WorksheetFunction.Count(trgRange(i))
Debug.Print "------------------------"
Next
End Sub＜結果＞
sum 1 10000
cnt 1 4
------------------------
sum 2 26000
cnt 2 4
------------------------
sum 3 42000
cnt 3 4
------------------------ちゃんと最初と同じ結果が得られましたね。trgRangeをどんどん拡張していって、最後にその集合に対してWorksheetFunctionで結果を取り出しています。Rangeオブジェクトのプロパティというわけではないのですが、ちょっとオブジェクト指向っぽいですね。格好いい書き方なんじゃないかと思います。
預金区分がちょうど数字だったのでそのまま配列の添え字に使ってみました。でもこれが通用するのは限定的でしょうね。
オブジェクト指向っぽいとか言い出したら次はClassを作ってSum、Count、AverageとかのPropertyを定義したくなってきますね。Unionで追加する処理とかもメソッドとして定義してあげるとメインのコードがとても今風になりそうです。まあそれはまた気が向いたら別の機会にでも実装するかもしれません。皆さんの「いいね」次第ですね🐮
追記
cr が既にRangeなので、
— りゅうりゅう (@blacklist_ryu) May 5, 2021
sum(cr.Cells(i, 1).Value) → sum(cr(i, 1).Value)
でも良さそう。
こんな的確なご指摘いただいたのでコード書き直しました。あざますっ！