Insert Blank rows after every date change

[josigrozi]

I have a very large worksheet.

I want to add 3 rows (one row for the sum the other two blanks) after each change of date and sum the value:

For Example

10 Jan2011. . . . 6
10 Jan2011 . . . . 8
10 Jan 2011. . . . 2
Total . . . . . . ... . 16
==============
==============
11 Jan2011. . . . 100
11 Jan2011 . . . . 100
11 Jan 2011. . . . 2
Total . . . . . . ... . 202
==============
==============
and so on...
Note : the equal sign I have used ====== to denote blank rows

Thanks for your help

[Leith Ross]

Hello josigrozi,

Welcome to the Forum!

Have you considered using Excel's built-in Subtotal function?

[josigrozi]

Thanks for your reply.

No I have not.... I do not know how to uset the subtotal function so that it is automated.
However the worksheet has about 4000 transactions with potential to increase at the rate of 500 a day.

[Leith Ross]

Hello josigrozi,

Doesn't sound like the Subtotal function is the way to go here. If the worksheet will updated every day then is the new data appended or completely overwritten? Is the data located in columns "A:C"?

[josigrozi]

The data will not be overwritten. At the moment the data is retrospective which means I am working on 6months worth of data.

The columns is from A: to H:

[Leith Ross]

Hello josigrozi,

I am not trying to be a pain. I just need more information to produce a workable solution.

If this is retrospective then you will add any additional data manually?

Do you want to use a shortcut key to run the macro?

Can you provide a workbook or before and after samples of what the data looks like?

[josigrozi]

Before:
$63.75 10-Jan-11 Details $63.75
$- 10-Jan-11 Details $66.00 $66.00
$- 10-Jan-11 Details $116.40 $116.40
$149.25 10-Jan-11 Details $149.25
$47.15 11-Jan-11 Details $47.15
$47.15 11-Jan-11 Details $47.15
$- 11-Jan-11 Details $70.20 $70.20
$- 11-Jan-11 Details $111.30 $111.30
$115.45 11-Jan-11 Details $115.45
$133.95 11-Jan-11 Details $133.95
$201.10 11-Jan-11 Details $201.10
$- 12-Jan-11 Details $141.45
$145.25 12-Jan-11 Details $145.25
$- 12-Jan-11 Details $158.45
$- 12-Jan-11 Details $188.60
$150.20 19-Jan-11 Details $150.20
$203.40 19-Jan-11 Details $203.40
$206.40 19-Jan-11 Details $206.40
$242.20 19-Jan-11 Details $242.20
$1,528.00 21-Jan-11 Details $1,528.00
$2,197.30 21-Jan-11 Details $2,197.30
$2,538.20 21-Jan-11 Details $2,538.20
$- 24-Jan-11 Details $11.65
$41.25 24-Jan-11 Details $41.25
$47.15 24-Jan-11 Details $47.15
$66.00 $70.20 $116.40 $111.30 $-
10-Jan-11 11-Jan-11 12-Jan-11 19-Jan-11 24-Jan-11
After:
10-Jan-11 Details $66.00
10-Jan-11 Details $63.75
10-Jan-11 Details $66.00
10-Jan-11 Details $116.40
10-Jan-11 Details $149.25
0k $461.40 $461.40

11-Jan-11 Details $70.20
11-Jan-11 Details $47.15
11-Jan-11 Details $115.45
11-Jan-11 Details $133.95
11-Jan-11 Details $201.10
0k $567.85 $567.85

12-Jan-11 Details $116.40
12-Jan-11 Details $188.60
0k $305.00 $305.00
0
19-Jan-11 Details $111.30
19-Jan-11 Details $111.30
19-Jan-11 Details $242.20
0k $464.80 $464.80

24-Jan-11 Details $-
24-Jan-11 Details $11.65
24-Jan-11 Details $41.25
24-Jan-11 Details $47.15
0k $100.05 $100.05

[josigrozi]

sorry I have lost the format

[josigrozi]

Please Login or Register  to view this content.

This is what I tried to copy but the result is that it is inserting one blank row after every line rather than after the date change.

[josigrozi]

Thanks for your suggestion. I checked the subtotal function, it is doing some of what is required.