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
--
caroline


"Diana" wrote:

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

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.

Diana wrote:

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

--

Dave Peterson

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"
Newsgroups: microsoft.public.excel.programming
Sent: Tuesday, February 23, 2010 11:57 PM
Subject: Slow subtotal and insert row


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


"Diana" wrote in message
...
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