神马文学网关于快速向下求和
来源：百度文库 编辑：神马文学网 时间：2024/04/27 13:34:40
棉花糖的代码
Sub sumdown()
Dim cell As Range, Rng As Range
On Error Resume Next
Set Rng = Intersect(Selection.SpecialCells(xlCellTypeBlanks), UsedRange)
If Err <> 0 Then Exit Sub
If Rng Is Nothing Then Exit Sub
For Each cell In Selection.SpecialCells(xlCellTypeBlanks)
cell.Formula = "=sum(" & Range(cell.Offset(1, 0).Address, cell.Offset(1, 0).End(xlDown).Address).Address(0, 0) & ")"
Next cell
Application.SendKeys "{F2}"
End Sub
这个cell In Selection.SpecialCells(xlCellTypeBlanks)意思
是 取得当前第n个空行向下的第1个单元格地址---》cell.Offset(1, 0).Address
是 取得当前第n个空行向下的非空的最后1个单元格-地址--》cell.Offset(1, 0).Address
所以用在 快速向下求和 这个不是很恰当
************************************
ub 向下求和()
Dim I As Integer, Rng As Range, Rng1 As Range
On Error Resume Next
Selection.ClearContents
For Each Rng1 In Selection
If Len(Rng1.Offset(1, 0)) = 0 Then GoTo 1
If Application.CountA(Range(Rng1.Offset(1, 0), Cells(Rows.Count, Rng1.Column))) = 0 Then End
Set Rng = Intersect(Rng1.Offset(1, 0).CurrentRegion, Range(Cells(1, Rng1.Column), Cells(Rows.Count, Rng1.Column)))
Rng1.Formula = "=SUM(" & Rng.Address(0, 0) & ")"
1:
Next
For Each Rng1 In Selection
If Len(Rng1) <> 0 Then Rng1.Activate: Exit For
Next
Application.SendKeys "{ctrl Z}"
End Sub