AutoSum an unknown range

[markmrw]

Hello
it has been a while since i posted last.

a possibly easy to solve problem for you all, but it has caused me much brain ache!

i am trying to AutoSum a range using VB but the range is not always the same.

the range i want to autosum Column E and F for each customer.
any help would be greatly appreciated, and i hope i have explained myself well enough?

Sub DelPerf()

Dim Ra As Range
Set Ra = Range("b3")

Do Until Ra.Value = ""
Set Ra = Ra.Offset(1)

'MsgBox ra.Value

If Ra.Value = Ra.Offset(-1) Then
Else: Ra.EntireRow.Select
Selection.Insert Shift:=xlDown
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Ra.Offset(-1).Value = Ra.Offset(-2)

'here is where i get stuck

End If

Loop

MsgBox "end"
End Sub

[Bob Phillips]

You say E & F but your code points at B which is all text.

I would simply insert a line in row 3, add a formula of

=SUM(E4:E10000)

and so on, solved, no VBA.

[markmrw]

thank you xld

i have been doing that for months now, it becomes tiresome
as i have to cut and paste everything to create my report, i have 4 sheets a week i have to do this with.

i am trying to automate the reports so that i can just click my button and it will do it all for me.

if you look at the sheet after the macro has run it inserts the Row and copys the customer name down.
and in this inserted row,
i would like it to only auto sum the "lines on order" column for each customer
not just the total.

"that would just be too easy and sensible"


hope i haven't confused too much

Mark

[Bob Phillips]

So, how about this



Sub AddTotals()

Application.DisplayAlerts = False
Cells.Subtotal GroupBy:=2, _
Function:=xlSum, _
TotalList:=Array(5, 6), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
End Sub

[markmrw]

my god thats fantastic.

exactly what i wanted, i dont even need all the "gubbins" that i had created.

Thank you XLD
a legend in your own underpants!

Mark