Sum variable row ranges 1

[PWD]

Good afternoon. I'm formatting a csv sheet that contains data exported from our trading system. This is separated out by the various branches and contains the totals for each branch. I have been asked to remove rows that contain a negative balance which means that the current total figures are now incorrect. I can't get my little brain around how to enter totals for each section. Below is an example of the sort of data I have with a variable number of rows in each section.

Customer	Due Now
A	£30.00
B	£4,576.80
C	£3,597.63
D	£15,248.07
	
Total:  Q 0911	£23,422.50
	
	
	
Customer	Due Now
E	£41.54
F	£1,240.75
G	£27.00
	
Total:  Q 9998	£1,282.29
	
	
	
Customer	Due Now
H	£50.00
I	£6,395.45
J	£60.00
K	£2,936.25
L	£2,542.02

Total:  Q 9999	£2,778.69

I've probably overlooked something obvious - I hope I have.

Thanks, Des.

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

subtotals......?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

How dreadful, having to scrape a report!. I'd move heaven and earth to find a valid source!

Short of that, I'd loop down thru the numeric values, using the CurrentRegion.Rows.Count method and End(xlDown) property to "group" each range to sum.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Good questions.

r = ActiveSheet.UsedRange.Rows.count

Range("A1").Select

‘I’m running down the rows – 

For i = 1 To r 

If Left(ActiveCell.Offset(i, 0), 5) = "Total" Then

ActiveCell.Offset(i, 13).Value = [The sum of the relevant rows until it stops like it would when using E on the toolbar]

Des.

 

[SkipVought]

Sub test()
    Dim rTotals As Range, lLastRow As Long, lRow As Long, cVal As Currency, rng As Range
    'assuming that column B contains your totals
    Set rTotals = Intersect(ActiveSheet.UsedRange, Range("B:B"))
    
    With ActiveSheet.UsedRange
      lLastRow = .Row + .Rows.Count - 1
      lRow = .Row
    End With
    
    Do
      If Cells(lRow, "A").Value = "Total:" Then
        Cells(lRow, "B").Value = cVal
        cVal = 0
        lRow = Cells(lRow, "A").End(xlDown).Row
      Else
        cVal = Application.Sum(Intersect(Cells(lRow, "B").CurrentRegion, rTotals))
        lRow = Cells(lRow, "A").End(xlDown).End(xlDown).Row
      End If
    Loop While lRow <= lLastRow
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Wow Skip! It's all rather elegant - no I've not been idle, just having a good look through the code trying to get a handle on it. I like the double

 .End(xlDown)

That works a treat. I’m just having a bit of trouble with the

 lRow = ActiveSheet.UsedRange.Row

as I’m not really sure what it achieves as it seems to return the value of 1. I’ve not used the

 Intersect

and

 CurrentRegion

before so this will encourage me to investigate their uses further. Many thanks, Des.

 

[SkipVought]

lRow = ActiveSheet.UsedRange.Row

is the first used row. It is not always row 1.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Also...

Current Region: There is a ToolBar Button that I recommend to my users, called Select Current Region

Right-Click the toolbar and select Customize...

Select the Edit Category and scroll Commands close to the bottom. There you will see a little black rectangle with ARROWS pointing out of the 4 corners Drag this icon to an active tool bar.

Play around with it. It acts the similar to the CirrentRegion method.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip; what a clever tool. It's now on my Personal.xls Formatting toolbar.

Thanks,

Des.