VBA for SUM and COUNTA

[Holley]

I have a macro setup that filters the amounts column E to only show rows with information and copies the filterd data from Spreadsheet 1 and creates a new spreadsheet as paste the data. There are 1000k rows in spreadsheet 1 but the number of rows that will be filtered will vary... could be 1 could be 60. I would like to sum the total in column E and count the number of rows that have information. I can get the sum, but having trouble doing both. I have searched the web looking for similar topics and found one that will help me total Column E, but I cannot figure out how to count the rows. In addition, If I could add Total to the preceding cell in column E, that would be very helpful.

Thanks in advance for any suggestions!

Sub With_Funds()
'
' With_Funds Macro
'

'
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$1220").AutoFilter Field:=5, Criteria1:=">=.01", _
        Operator:=xlAnd
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Columns.AutoFit
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "$#,##0.00"
        Dim Rng As Range
    Dim c As Range
    Set Rng = Range("E1:E" & Range("E1").End(xlDown).Row)
    Set c = Range("E1").End(xlDown).Offset(1, 0)
    c.Formula = "=SUM(" & Rng.Address(False, False) & ")"
  
End Sub

 

[DanteAmor]

Check if the following is what you need:

Sub With_Funds_v1()
  Dim lr As Long
  
  Application.ScreenUpdating = False
  lr = Range("E" & Rows.Count).End(3).Row
  ActiveSheet.Range("$A$1:$S$" & lr).AutoFilter Field:=5, Criteria1:=">=.01"
  ActiveSheet.AutoFilter.Range.Copy
  
  Workbooks.Add
  Selection.PasteSpecial Paste:=xlPasteValues
  Selection.Columns.AutoFit
  Columns("E:E").NumberFormat = "$#,##0.00"
  lr = Range("E" & Rows.Count).End(3).Row
  With Range("E" & lr)
    .Offset(1).Formula = "=Sum(E1:E" & lr & ")"
    .Offset(1, -1).Value = "Sum"
    .Offset(2).Formula = "=Count(E1:E" & lr & ")"
    .Offset(2).NumberFormat = "General"
    .Offset(2, -1).Value = "Count"
  End With
  Application.ScreenUpdating = True
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Holley]

You, my friend, ARE A GENIUS! THANK YOU SO MUCH! This perfectly solved my issue!! Most appreciated!