EXCEL VBA 最終行・最終列・合計行・合計列の作成・SUM関数追加（テクニック）

2020年1月6日

EXCEL VBA 最終行・最終列・合計行・合計列の作成・SUM関数追加（テクニック）

●はじめに

EXCELで一覧表や集計表を作成すると、縦に合計列・横に合計行を作成すると思います。通常EXCELで作成する場合は、SUM関数を利用して合計行を作成しますが、EXCEL VBAで合計を作成する際もEXCELと同様に、SUM関数を利用した方が途中に行列を挿入した際に、再計算する必要が無くSUM関数で対応できるので、どちらかと言うとお勧めします。なお、今回は下記に作成したVBAでSUM関数を利用するプログラムです。集計表の大きさに応じて合計行・合計列を自動作成する様にプログラムにて対応していますので、行列追加による変更にも対応しています。それでは、SUM関数を利用した合計行・合計列の作成方法を説明いたします。

EXCEL VBA 最終行・合計行の作成・SUM関数追加（テクニック）

●プログラム説明　(サンプルプログラム①)

下記のサンプルプログラムは、下表の最終行にSUM関数を作成するサンプルプログラムです。表の最終行（16行）を把握して、セル「C16」～「H16」までの間に、Sum関数を代入します。

Sub 最終行合計追加()

    Dim lRow As Long
    
    lRow = Cells(Rows.Count, "B").End(xlUp).Row  'B列の最終行を取得
    
    
    With Range("C" & lRow + 1)
        .Interior.ColorIndex = 15
        .Formula = "=SUM(C6:C" & lRow & ")"   'C列の最終行にSUM関数「C6～最終行」を代入
        .AutoFill Destination:=.Resize(1, 6) 'AutoFillを使い横６列分複写する
    
    End With
    
    Range("A5:H" & lRow + 1).Borders.LineStyle = xlContinuous 'A5～Hの最終行まで罫線を引く

    
    Application.DisplayAlerts = False 'セルを結合する時に警告を無視します。
    Range("A" & lRow + 1 & ":B" & lRow + 1).MergeCells = True '最終行のA.B列を結合する
    
    With Range("A" & lRow + 1)
        .Formula = "合　計"  '結合した最終行A列に”合計”を代入
        .HorizontalAlignment = xlCenter 'A列の合計行の”合計”を中央揃え
    End With
    
    Application.DisplayAlerts = True '警告を解除します。
   
End Sub

Sub 最終行合計追加()

Dim lRow As Long

lRow = Cells(Rows.Count, "B").End(xlUp).Row 'B列の最終行を取得

With Range("C" & lRow + 1)

.Interior.ColorIndex = 15

.Formula = "=SUM(C6:C" & lRow & ")" 'C列の最終行にSUM関数「C6～最終行」を代入

.AutoFill Destination:=.Resize(1, 6) 'AutoFillを使い横６列分複写する

End With

Range("A5:H" & lRow + 1).Borders.LineStyle = xlContinuous 'A5～Hの最終行まで罫線を引く

Application.DisplayAlerts = False 'セルを結合する時に警告を無視します。

Range("A" & lRow + 1 & ":B" & lRow + 1).MergeCells = True '最終行のA.B列を結合する

With Range("A" & lRow + 1)

.Formula = "合　計" '結合した最終行A列に”合計”を代入

.HorizontalAlignment = xlCenter 'A列の合計行の”合計”を中央揃え

End With

Application.DisplayAlerts = True '警告を解除します。

End Sub

●実行前～実行後　※実行後、最終行（１６行）に「合計行」としてSUM関数が代入されました。

EXCEL VBA 最終列・合計列の作成・SUM関数追加（テクニック）

●プログラム説明　(サンプルプログラム②)

下記のサンプルプログラムは、下表の最終列にSUM関数を作成するサンプルプログラムです。表の最終列（I列）を把握して、セル「I6」～「I15」までの間に、Sum関数を代入します。

Sub 最終列合計追加()

    Dim ColE, lrow As Long
    Dim LCol, XCol, NCol As String
    
    
    ColE = Cells(5, Columns.Count).End(xlToLeft).Column '5行目の最終列を取得
    
    LCol = Cells(6, ColE).Address  '6行目の最終列のセル番号を取得（"I6"）
    
    XCol = Cells(6, ColE - 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)  'SUM関数で使う最終列-1のセル番号を取得（"H6"）
    
    lrow = Cells(Rows.Count, "B").End(xlUp).Row  'B列の最終行を取得
    
    With Range(LCol)
        .Interior.ColorIndex = 15
        .Formula = "=SUM(C6:" & XCol & ")"   '最終行にSUM関数「C6～最終列」を代入
        .AutoFill Destination:=.Resize(10, 1) 'AutoFillを使い横６列分複写する
    
    End With
    
    Range("A5:I" & lrow).Borders.LineStyle = xlContinuous 'A5～Iの最終行まで罫線を引く

    
    NCol = Cells(5, ColE).Address '5行目の最終行のセル番号を取得
    
    With Range(NCol)
        .Formula = "合　計"  'I列先頭行に”合計”を代入
        .HorizontalAlignment = xlCenter 'I列の先頭行合計”を中央揃え
        .Interior.ColorIndex = 15  '背景色を付ける。
    End With
    
End Sub