I want to create a macro in a spreadsheet that has been sorted in column H.
I want to put in 2 blank rows between different groups of data and then sum
up the value of that data in column J for that group in the 1st blank row
that was created.

I think this means reading the information in cell H2 and comparing cells
down the column until it does not equal the value in H2. I want to then
insert 2 blank rows and them sum up the values in column J for that group on
the 1st blank row that was created.

I want to retain the column H information and then compare it down the
column and repeat the process.

Does anyone have a sample I can use?

Sub AAA()
Dim start As Range, rng As Range
Dim rngo As Range, i As Long
Set start = Range("H2")
i = 3
Do
Set rng = Cells(i, "H")
Set rngo = Cells(i - 1, "H")
If rng.Value < rngo.Value Then
rng.Resize(2).EntireRow.Insert
rngo.Offset(1, 1).Formula = "=Sum(" & _
Range(start, rngo).Offset(0, 1).Address & ")"
i = i + 2
Set start = Cells(i, "H")
End If
i = i + 1
If Cells(i, "H") = "" Then Exit Do
Loop
End Sub

worked for me. Test it on a copy of your data.

--
Regards,
Tom Ogilvy

"lpdarspe" wrote:

I want to create a macro in a spreadsheet that has been sorted in column H.
I want to put in 2 blank rows between different groups of data and then sum
up the value of that data in column J for that group in the 1st blank row
that was created.

I think this means reading the information in cell H2 and comparing cells
down the column until it does not equal the value in H2. I want to then
insert 2 blank rows and them sum up the values in column J for that group on
the 1st blank row that was created.

I want to retain the column H information and then compare it down the
column and repeat the process.

Does anyone have a sample I can use?

Did check the results closely enough - that was skipping the last set of
data. This slight modification seems to fix that:

Sub AAA()
Dim start As Range, rng As Range
Dim rngo As Range, i As Long
Set start = Range("H2")
i = 3
Do
Set rng = Cells(i, "H")
Set rngo = Cells(i - 1, "H")
If rng.Value < rngo.Value Then
rng.Resize(2).EntireRow.Insert
rngo.Offset(1, 1).Formula = "=Sum(" & _
Range(start, rngo).Offset(0, 1).Address & ")"
i = i + 2
Set start = Cells(i, "H")
End If
i = i + 1
If Cells(i - 1, "H") = "" Then Exit Do
Loop
End Sub
--
Regards,
Tom Ogilvy


"lpdarspe" wrote:

I want to create a macro in a spreadsheet that has been sorted in column H.
I want to put in 2 blank rows between different groups of data and then sum
up the value of that data in column J for that group in the 1st blank row
that was created.

I think this means reading the information in cell H2 and comparing cells
down the column until it does not equal the value in H2. I want to then
insert 2 blank rows and them sum up the values in column J for that group on
the 1st blank row that was created.

I want to retain the column H information and then compare it down the
column and repeat the process.

Does anyone have a sample I can use?

Sub h()
lastrow = Cells(Rows.Count, "h").End(xlUp).Row
r = 1 ' <=== change start row of data
Do
n = Application.CountIf(Range("H:H"), Cells(r, "H"))
Cells(r + n, "A").Resize(2, 1).EntireRow.Insert
Cells(r + n, "I") = Application.Sum(Range(Cells(r, "h"), Cells(r + n - 1,
"H")))
r = r + n + 2
Loop Until r lastrow
Cells(r + n, "I") = Application.Sum(Range(Cells(r, "h"), Cells(r + n - 1,
"H")))

End Sub

"lpdarspe" wrote:

I want to create a macro in a spreadsheet that has been sorted in column H.
I want to put in 2 blank rows between different groups of data and then sum
up the value of that data in column J for that group in the 1st blank row
that was created.

I think this means reading the information in cell H2 and comparing cells
down the column until it does not equal the value in H2. I want to then
insert 2 blank rows and them sum up the values in column J for that group on
the 1st blank row that was created.

I want to retain the column H information and then compare it down the
column and repeat the process.

Does anyone have a sample I can use?

Re-reading I see you want column J and I am doing column I. Here is a
revision:

Sub AAA()
Dim start As Range, rng As Range
Dim rngo As Range, i As Long
Set start = Range("H2")
i = 3
Do
Set rng = Cells(i, "H")
Set rngo = Cells(i - 1, "H")
If rng.Value < rngo.Value Then
rng.Resize(2).EntireRow.Insert
rngo.Offset(1, 2).Formula = "=Sum(" & _
Range(start, rngo).Offset(0, 2).Address & ")"
i = i + 2
Set start = Cells(i, "H")
End If
i = i + 1
If Cells(i - 1, "H") = "" Then Exit Do
Loop
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Did check the results closely enough - that was skipping the last set of
data. This slight modification seems to fix that:

Sub AAA()
Dim start As Range, rng As Range
Dim rngo As Range, i As Long
Set start = Range("H2")
i = 3
Do
Set rng = Cells(i, "H")
Set rngo = Cells(i - 1, "H")
If rng.Value < rngo.Value Then
rng.Resize(2).EntireRow.Insert
rngo.Offset(1, 1).Formula = "=Sum(" & _
Range(start, rngo).Offset(0, 1).Address & ")"
i = i + 2
Set start = Cells(i, "H")
End If
i = i + 1
If Cells(i - 1, "H") = "" Then Exit Do
Loop
End Sub
--
Regards,
Tom Ogilvy


"lpdarspe" wrote:

I want to create a macro in a spreadsheet that has been sorted in column H.
I want to put in 2 blank rows between different groups of data and then sum
up the value of that data in column J for that group in the 1st blank row
that was created.

I think this means reading the information in cell H2 and comparing cells
down the column until it does not equal the value in H2. I want to then
insert 2 blank rows and them sum up the values in column J for that group on
the 1st blank row that was created.

I want to retain the column H information and then compare it down the
column and repeat the process.

Does anyone have a sample I can use?

.... as per Tom's note ... change my column to J!

"Toppers" wrote:

Sub h()
lastrow = Cells(Rows.Count, "h").End(xlUp).Row
r = 1 ' <=== change start row of data
Do
n = Application.CountIf(Range("H:H"), Cells(r, "H"))
Cells(r + n, "A").Resize(2, 1).EntireRow.Insert
Cells(r + n, "I") = Application.Sum(Range(Cells(r, "h"), Cells(r + n - 1,
"H")))
r = r + n + 2
Loop Until r lastrow
Cells(r + n, "I") = Application.Sum(Range(Cells(r, "h"), Cells(r + n - 1,
"H")))

End Sub

"lpdarspe" wrote:

I want to create a macro in a spreadsheet that has been sorted in column H.
I want to put in 2 blank rows between different groups of data and then sum
up the value of that data in column J for that group in the 1st blank row
that was created.

I think this means reading the information in cell H2 and comparing cells
down the column until it does not equal the value in H2. I want to then
insert 2 blank rows and them sum up the values in column J for that group on
the 1st blank row that was created.

I want to retain the column H information and then compare it down the
column and repeat the process.

Does anyone have a sample I can use?

Tom-thank you very much. It worked perfectly!!

"Tom Ogilvy" wrote:

Re-reading I see you want column J and I am doing column I. Here is a
revision:

Sub AAA()
Dim start As Range, rng As Range
Dim rngo As Range, i As Long
Set start = Range("H2")
i = 3
Do
Set rng = Cells(i, "H")
Set rngo = Cells(i - 1, "H")
If rng.Value < rngo.Value Then
rng.Resize(2).EntireRow.Insert
rngo.Offset(1, 2).Formula = "=Sum(" & _
Range(start, rngo).Offset(0, 2).Address & ")"
i = i + 2
Set start = Cells(i, "H")
End If
i = i + 1
If Cells(i - 1, "H") = "" Then Exit Do
Loop
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Did check the results closely enough - that was skipping the last set of
data. This slight modification seems to fix that:

Sub AAA()
Dim start As Range, rng As Range
Dim rngo As Range, i As Long
Set start = Range("H2")
i = 3
Do
Set rng = Cells(i, "H")
Set rngo = Cells(i - 1, "H")
If rng.Value < rngo.Value Then
rng.Resize(2).EntireRow.Insert
rngo.Offset(1, 1).Formula = "=Sum(" & _
Range(start, rngo).Offset(0, 1).Address & ")"
i = i + 2
Set start = Cells(i, "H")
End If
i = i + 1
If Cells(i - 1, "H") = "" Then Exit Do
Loop
End Sub
--
Regards,
Tom Ogilvy


"lpdarspe" wrote:

I want to create a macro in a spreadsheet that has been sorted in column H.
I want to put in 2 blank rows between different groups of data and then sum
up the value of that data in column J for that group in the 1st blank row
that was created.

I think this means reading the information in cell H2 and comparing cells
down the column until it does not equal the value in H2. I want to then
insert 2 blank rows and them sum up the values in column J for that group on
the 1st blank row that was created.

I want to retain the column H information and then compare it down the
column and repeat the process.

Does anyone have a sample I can use?

Toppers-it did not quite work like it was supposed to. It created 2 blank
rows directly below the first row.

"Toppers" wrote:

Sub h()
lastrow = Cells(Rows.Count, "h").End(xlUp).Row
r = 1 ' <=== change start row of data
Do
n = Application.CountIf(Range("H:H"), Cells(r, "H"))
Cells(r + n, "A").Resize(2, 1).EntireRow.Insert
Cells(r + n, "I") = Application.Sum(Range(Cells(r, "h"), Cells(r + n - 1,
"H")))
r = r + n + 2
Loop Until r lastrow
Cells(r + n, "I") = Application.Sum(Range(Cells(r, "h"), Cells(r + n - 1,
"H")))

End Sub

"lpdarspe" wrote:

I want to create a macro in a spreadsheet that has been sorted in column H.
I want to put in 2 blank rows between different groups of data and then sum
up the value of that data in column J for that group in the 1st blank row
that was created.

I think this means reading the information in cell H2 and comparing cells
down the column until it does not equal the value in H2. I want to then
insert 2 blank rows and them sum up the values in column J for that group on
the 1st blank row that was created.

I want to retain the column H information and then compare it down the
column and repeat the process.

Does anyone have a sample I can use?

This will happen if the first row is your header. If it is, set r=2.

"lpdarspe" wrote:

Toppers-it did not quite work like it was supposed to. It created 2 blank
rows directly below the first row.

"Toppers" wrote:

Sub h()
lastrow = Cells(Rows.Count, "h").End(xlUp).Row
r = 1 ' <=== change start row of data
Do
n = Application.CountIf(Range("H:H"), Cells(r, "H"))
Cells(r + n, "A").Resize(2, 1).EntireRow.Insert
Cells(r + n, "I") = Application.Sum(Range(Cells(r, "h"), Cells(r + n - 1,
"H")))
r = r + n + 2
Loop Until r lastrow
Cells(r + n, "I") = Application.Sum(Range(Cells(r, "h"), Cells(r + n - 1,
"H")))

End Sub

"lpdarspe" wrote:

I want to create a macro in a spreadsheet that has been sorted in column H.
I want to put in 2 blank rows between different groups of data and then sum
up the value of that data in column J for that group in the 1st blank row
that was created.

I think this means reading the information in cell H2 and comparing cells
down the column until it does not equal the value in H2. I want to then
insert 2 blank rows and them sum up the values in column J for that group on
the 1st blank row that was created.

I want to retain the column H information and then compare it down the
column and repeat the process.

Does anyone have a sample I can use?