Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub

I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.

Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19

13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93

02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94

29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16

Please advise if you require any further info.

Best Regards,
Kam.

How about an alternative?

Insert a new column that retrieves the year from the date:
=year(a2)
Then drag down as far as you need.

Then you can sort your data and use Data|Subtotals to get your total rows.

Another option would be to learn data|pivottable.

You can create some very nice summary tables and group your dates by year.

Kam wrote:

Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub

I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.

Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19

13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93

02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94

29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16

Please advise if you require any further info.

Best Regards,
Kam.

--

Dave Peterson

Actually I want total of both (USD & GBP) in each blank rows using VBA code...

"Dave Peterson" wrote:

How about an alternative?

Insert a new column that retrieves the year from the date:
=year(a2)
Then drag down as far as you need.

Then you can sort your data and use Data|Subtotals to get your total rows.

Another option would be to learn data|pivottable.

You can create some very nice summary tables and group your dates by year.

Kam wrote:

Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub

I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.

Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19

13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93

02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94

29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16

Please advise if you require any further info.

Best Regards,
Kam.

--

Dave Peterson

I think you got confused with my comments..I have below mentioned script that
will add rows after years changes i.e 2004,2005,2006 & 2007 but I want to add
sub totals after inserting rows.

"Dave Peterson" wrote:

How about an alternative?

Insert a new column that retrieves the year from the date:
=year(a2)
Then drag down as far as you need.

Then you can sort your data and use Data|Subtotals to get your total rows.

Another option would be to learn data|pivottable.

You can create some very nice summary tables and group your dates by year.

Kam wrote:

Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub

I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.

Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19

13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93

02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94

29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16

Please advise if you require any further info.

Best Regards,
Kam.

--

Dave Peterson

I wasn't confused. I just figured that you're trying to create a macro that
does pretty much what data|subtotal does.



Kam wrote:

I think you got confused with my comments..I have below mentioned script that
will add rows after years changes i.e 2004,2005,2006 & 2007 but I want to add
sub totals after inserting rows.

"Dave Peterson" wrote:

How about an alternative?

Insert a new column that retrieves the year from the date:
=year(a2)
Then drag down as far as you need.

Then you can sort your data and use Data|Subtotals to get your total rows.

Another option would be to learn data|pivottable.

You can create some very nice summary tables and group your dates by year.

Kam wrote:

Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub

I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.

Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19

13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93

02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94

29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16

Please advise if you require any further info.

Best Regards,
Kam.

--

Dave Peterson


--

Dave Peterson

I mean to say that my english is poor...so sorry for that...Is it possible to
add subtotals on each blank rows using VBA code....

"Dave Peterson" wrote:

I wasn't confused. I just figured that you're trying to create a macro that
does pretty much what data|subtotal does.



Kam wrote:

I think you got confused with my comments..I have below mentioned script that
will add rows after years changes i.e 2004,2005,2006 & 2007 but I want to add
sub totals after inserting rows.

"Dave Peterson" wrote:

How about an alternative?

Insert a new column that retrieves the year from the date:
=year(a2)
Then drag down as far as you need.

Then you can sort your data and use Data|Subtotals to get your total rows.

Another option would be to learn data|pivottable.

You can create some very nice summary tables and group your dates by year.

Kam wrote:

Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub

I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.

Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19

13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93

02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94

29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16

Please advise if you require any further info.

Best Regards,
Kam.

--

Dave Peterson


--

Dave Peterson

I'm sure there is.

But you may find recording a macro when you remove the existing data|subtotals
and then reapplying data|subtotals does what you want and is easier to
code/record.

Kam wrote:

I mean to say that my english is poor...so sorry for that...Is it possible to
add subtotals on each blank rows using VBA code....

"Dave Peterson" wrote:

I wasn't confused. I just figured that you're trying to create a macro that
does pretty much what data|subtotal does.



Kam wrote:

I think you got confused with my comments..I have below mentioned script that
will add rows after years changes i.e 2004,2005,2006 & 2007 but I want to add
sub totals after inserting rows.

"Dave Peterson" wrote:

How about an alternative?

Insert a new column that retrieves the year from the date:
=year(a2)
Then drag down as far as you need.

Then you can sort your data and use Data|Subtotals to get your total rows.

Another option would be to learn data|pivottable.

You can create some very nice summary tables and group your dates by year.

Kam wrote:

Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub

I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.

Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19

13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93

02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94

29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16

Please advise if you require any further info.

Best Regards,
Kam.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Yes I know that it is possible my using by recording macros but it won't be
suceessful every time bcoz blanks rows varies...

"Kam" wrote:

Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub

I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.

Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19

13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93

02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94

29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16

Please advise if you require any further info.

Best Regards,
Kam.

Hi I have tried to explain in simple way....Please Please give me macro which
can do that...please....please...

here is my problem ...

I have a worksheet with data in it .. in column"A" I have a date value ...
what i would like to do .. is

1) insert a row after a certain criteria is met ie.

Column A Column H
row 1 = 07/07/2004 100
row 2 = 23/07/2004 50
row 3 = 13/06/2005 30
row 4 = 01/09/2005 20
row 5 = 27/12/2005 10
row 6 = 02/02/2006 05
row 7 = 14/02/2006 07

I would want to insert a row between row 2 and 3 then 5 & 6...&...So
on...when year in dates change

2) I would like to total the numbers in column H above the column that I
just inserted

so basically trying to do a grouping with totals.

any help would be appreciated.

"Kam" wrote:

Yes I know that it is possible my using by recording macros but it won't be
suceessful every time bcoz blanks rows varies...

"Kam" wrote:

Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub

I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.

Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19

13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93

02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94

29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16

Please advise if you require any further info.

Best Regards,
Kam.

Maybe someone else will jump in.

Good luck.

Kam wrote:

Hi I have tried to explain in simple way....Please Please give me macro which
can do that...please....please...

here is my problem ...

I have a worksheet with data in it .. in column"A" I have a date value ...
what i would like to do .. is

1) insert a row after a certain criteria is met ie.

Column A Column H
row 1 = 07/07/2004 100
row 2 = 23/07/2004 50
row 3 = 13/06/2005 30
row 4 = 01/09/2005 20
row 5 = 27/12/2005 10
row 6 = 02/02/2006 05
row 7 = 14/02/2006 07

I would want to insert a row between row 2 and 3 then 5 & 6...&...So
on...when year in dates change

2) I would like to total the numbers in column H above the column that I
just inserted

so basically trying to do a grouping with totals.

any help would be appreciated.

"Kam" wrote:

Yes I know that it is possible my using by recording macros but it won't be
suceessful every time bcoz blanks rows varies...

"Kam" wrote:

Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub

I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.

Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19

13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93

02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94

29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16

Please advise if you require any further info.

Best Regards,
Kam.

--

Dave Peterson

Thanks Dave....Can anyone help please....

"Dave Peterson" wrote:

Maybe someone else will jump in.

Good luck.

Kam wrote:

Hi I have tried to explain in simple way....Please Please give me macro which
can do that...please....please...

here is my problem ...

I have a worksheet with data in it .. in column"A" I have a date value ...
what i would like to do .. is

1) insert a row after a certain criteria is met ie.

Column A Column H
row 1 = 07/07/2004 100
row 2 = 23/07/2004 50
row 3 = 13/06/2005 30
row 4 = 01/09/2005 20
row 5 = 27/12/2005 10
row 6 = 02/02/2006 05
row 7 = 14/02/2006 07

I would want to insert a row between row 2 and 3 then 5 & 6...&...So
on...when year in dates change

2) I would like to total the numbers in column H above the column that I
just inserted

so basically trying to do a grouping with totals.

any help would be appreciated.

"Kam" wrote:

Yes I know that it is possible my using by recording macros but it won't be
suceessful every time bcoz blanks rows varies...

"Kam" wrote:

Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub

I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.

Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19

13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93

02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94

29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16

Please advise if you require any further info.

Best Regards,
Kam.

--

Dave Peterson

Can any help please if it not possible to make this script then it's
fine...but please reply......

"Kam" wrote:

Thanks Dave....Can anyone help please....

"Dave Peterson" wrote:

Maybe someone else will jump in.

Good luck.

Kam wrote:

Hi I have tried to explain in simple way....Please Please give me macro which
can do that...please....please...

here is my problem ...

I have a worksheet with data in it .. in column"A" I have a date value ...
what i would like to do .. is

1) insert a row after a certain criteria is met ie.

Column A Column H
row 1 = 07/07/2004 100
row 2 = 23/07/2004 50
row 3 = 13/06/2005 30
row 4 = 01/09/2005 20
row 5 = 27/12/2005 10
row 6 = 02/02/2006 05
row 7 = 14/02/2006 07

I would want to insert a row between row 2 and 3 then 5 & 6...&...So
on...when year in dates change

2) I would like to total the numbers in column H above the column that I
just inserted

so basically trying to do a grouping with totals.

any help would be appreciated.

"Kam" wrote:

Yes I know that it is possible my using by recording macros but it won't be
suceessful every time bcoz blanks rows varies...

"Kam" wrote:

Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub

I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.

Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19

13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93

02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94

29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16

Please advise if you require any further info.

Best Regards,
Kam.

--

Dave Peterson