Slow subtotal and insert row

  • Thread starter Thread starter Diana
  • Start date Start date
D

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
 
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
 
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.
 
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
 
Back
Top