VBA SUMIF on data dump with various clients and lengths

[peng_p]

I'd like a VBA for a data dump I get from one of the systems... it generates by each client with various lengths of data under one another. When imported to Excel, the totals are not in their proper cells as you can see in the example provided. The headers for each client is constant throughout the entire spreadsheet as well as the totals row beginning with "Cli:"-- I'm familiar with inputting a SUMIF formula directly into the spreadsheet with a limited amount of data, just not into a VBA for multiple sets of data. (The one I am using as a test has over 10,000 lines, but each client is separated by an empty row as seen in the example below.

What I want to do:
1. Find each "Cli: * Currency CAD:" in column A and replace value with sum of each client section in columns F, G, H, N, O, P, and CC.
2. Insert rows at the top or bottom of the spreadsheet to get a grand total for all clients.

 

[DanteAmor]

With 10,000 records it takes 90 seconds, that's because it uses the sheet functions.
If you want a faster process, then I would have to do a more elaborate macro with memory process.

Try and comment

Sub Macro3()
  Dim a As Range
  Application.ScreenUpdating = False
  For Each a In Range("A1", Range("A" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants, 23).Areas
    With a.Range(Replace("F@,G@,H@,N@,O@,P@,CC@", "@", a.Rows.Count))
      .Formula = "=SUM(" & a.Cells(1, "F").Offset(2).Resize(a.Rows.Count - 3).Address(0, 0) & ")"
      a.Rows(a.Rows.Count).EntireRow.Value = a.Rows(a.Rows.Count).EntireRow.Value
    End With
  Next
End Sub

 

[peng_p]

Oh, that is interesting. So the VBA searchs for the last row of "each section" and then applies the SUM formula. By passes the idea of the constant "Cli: * Currency CAD:" text. Is that how I am reading the code?

Also, for educational purposes-- if you have the time for a more elaborate macro with memory process... I would be interested to see what that looks like.

Thank you very much!

 

[DanteAmor]

So the VBA searchs for the last row of "each section" and then applies the SUM formula.

That's right. But it does not use the constant "Cli", what it uses is the concept "areas", that is, it selects by data block until it finds a blank cell.