Slow subtotal and insert row

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
 
C

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
 
D

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.
 
H

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top