Slow subtotal and insert row

[Diana]

Hi,

I have a worksheet with about 400 rows. Data looks like:

Name calls
Peter 5
John 3
David 9
Jenny 1
Gina 10
Peter 2
Gina 6
Mary 6
David 3

I want the number of calls made by each person.

So what I have done is a sort, then a subtotal, then insert a row after the
subtotal to make it look better.

I find the code for the calculation of the subtotal and insert run very
slowly (takes about a minute. I can see the screen flickering and the rows
being inserted). Is there a way I can make it run faster?

One other question:

In my code I refer to the columns by the letter eg, "J", "K", is there a way
I can tell it to use column headers, eg. Name, calls

 

[caroline]

It is slow because it calculates while inserting the rows.

call calcmanual before your code
and then calcautomatic after your code
See below

Alternatively why not using a sumif function without any code involved?


Sub CalcAutomatic()
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Calculate

End Sub
Sub CalcManual()

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

End Sub

 

[Dave Peterson]

If you're only trying to make it look better, then you may want to just adjust
the row height for the subtotal rows.

It may make anything else you end up doing a little easier.

 

[helene and gabor]

Hello Diana,

I took your data and succeeded in obtaining a report of calls for each of
the people.
The table must have headers like: name, calls.
With Excel 2007 you click insert, table, create pivot table, specify the
location as say G1:H24. Choose fields to add to report:name and calls.
This will create you your output of all names and the associated number of
calls made by each person.

Best Regards,

Gabor Sebo
----- Original Message -----
From: "Diana" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Tuesday, February 23, 2010 11:57 PM
Subject: Slow subtotal and insert row