如下面的数据,最下面的一行是汇总行,当在汇总行上面插入行时,汇总的数据并不会动态地改变,这对于正常的数据统计来说有时是一个困扰。
我们可以通过VBA代码,动态地对数据进行汇总:
1 对特定的一列或某些列进行动态汇总:
1.1 写一个以列标字母为参数的子过程
Sub dynamicSumFun(col As String)
Dim lastR As Integer
Dim lastC As Integer
Dim flag As Boolean
lastR = Cells(Rows.Count, 1).End(xlUp).Row
lastC = Cells(1, Columns.Count).End(xlToLeft).Column
Range("B" & lastR + 1).Value = "汇总:"
Range(col & lastR + 1).Formula = "=sum(" & col & "2:" & col & lastR & ")"
End Sub
1.2 以特定的列标字母为参数调用子过程
Sub dynamicSum()
Call dynamicSumFun("E")
End Sub
用不同的参数调用多次:
Sub dynamicSum2()
Call dynamicSumFun("E")
Call dynamicSumFun("G")
End Sub
2 对全部的数值列动态汇总
以第二行为判断依据,对第二行内的每一个单元格进行判断,IsNumeric()函数判断为True及非空的单元格进行动态汇总。
Sub dynamicSumFun2()
Dim lastR As Integer
Dim lastC As Integer
Dim flag As Boolean
Dim i As Integer
lastR = Cells(Rows.Count, 1).End(xlUp).Row
lastC = Cells(1, Columns.Count).End(xlToLeft).Column
Range("B" & lastR + 1).Value = "汇总:"
For i = 3 To lastC
flag = VBA.IsNumeric(Cells(2, i)) And Cells(2, i) <> ""
Debug.Print (flag)
If flag = True Then
col = colsChar(i)
Cells(lastR + 1, i).Formula = "=sum(" & col & "2:" & col & lastR & ")"
End If
Next i
End Sub
'数字转换为列标的函数,用于上面过程的调用
Function colsChar(i As Integer)
Dim j As Integer
Dim s As String
s = Cells(1, i).Address
j = InStrRev(s, "$")
s = Left(s, j - 1)
colsChar = Right(s, Len(s) - 1)
End Function
_End_