很好用的流水账（录入数据或者通过系统自动筛选自动复制余额、合计相应数据列、添...

feiaoli

通过mmwwdd老师、wowo000老师不厌其烦的大力的无私帮助，做了一个很好用的流水账，仅此分享表达对两位老师的感激之情！

同时也感谢其他老师的无私帮助！

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2. If FilterMode = True Then
   
3.     arr = Sheet1.UsedRange
   
4.     r = UBound(arr)
   
5.     If Cells(r, 5).HasFormula = False Then
   
6.        With Cells(r + 1, 1)
   
7.        .Value = "合    计"
   
8.        .Resize(1, 4).HorizontalAlignment = xlHAlignCenterAcrossSelection
   
9.        End With
   
10.        Range("E" & r + 1 & ":F" & r + 1).FormulaR1C1 = "=subtotal(9,r2c:r[-1]c)"
    
11.        Range("G" & r + 1).FormulaR1C1 = "=SUBTOTAL(109,R2C5:RC[-2])-SUBTOTAL(109,R2C6:RC[-1])"
    
12.        Range("A" & r + 1 & ":G" & r + 1).Interior.ColorIndex = 44
    
13.     End If
    
14. End If
    
15. If FilterMode = False Then
    
16.     arr = Sheet1.UsedRange
    
17.     r = UBound(arr)
    
18.     If Cells(r, 5).HasFormula = True Then
    
19.        Cells(r, 1).EntireRow.Delete
    
20.     End If
    
21. End If
    
22.     r = Cells(Rows.Count, 1).End(xlUp).Row
    
23.     C = Cells(1, Columns.Count).End(xlToLeft).Column
    
24.     Range("G" & r).FormulaR1C1 = "=SUBTOTAL(109,R2C5:RC[-2])-SUBTOTAL(109,R2C6:RC[-1])"
    
25. End Sub

复制代码

游乐缘

感谢分享，前排留名学习