I have the below code in a macro that by itself runs great. I want to stop it
at row 8. The data starts in row 9 and ends when it ends - the amount of rows
always varies. I have tried using different "Do", "Do Until" but have not
been successful. Any help would be greatly appreciated.

Darryl


Range("a8").Select

Dim lastrow As Long, formRow As Long
Dim i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
formRow = lastrow + 1
For i = lastrow To 2 Step -1
If Cells(i, 31) < Cells(i - 1, 31) Then
Cells(formRow, "K").Formula = "=SubTotal(9," & _
"K" & i & ":K" & formRow - 1 & ")"
Rows(i).insert shift:=xlShiftDown
formRow = i

End If

Next

Range("a8").Select

Dim lastrow As Long, formRow As Long
Dim i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
formRow = lastrow + 1
For i = lastrow To 10 Step -1
If Cells(i, 31) < Cells(i - 1, 31) Then
Cells(formRow, "K").Formula = "=SubTotal(9," & _
"K" & i & ":K" & formRow - 1 & ")"
Rows(i).insert shift:=xlShiftDown
formRow = i

End If

Next

--
Regards,
Tom Ogilvy



"Darryl" wrote:

I have the below code in a macro that by itself runs great. I want to stop it
at row 8. The data starts in row 9 and ends when it ends - the amount of rows
always varies. I have tried using different "Do", "Do Until" but have not
been successful. Any help would be greatly appreciated.

Darryl


Range("a8").Select

Dim lastrow As Long, formRow As Long
Dim i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
formRow = lastrow + 1
For i = lastrow To 2 Step -1
If Cells(i, 31) < Cells(i - 1, 31) Then
Cells(formRow, "K").Formula = "=SubTotal(9," & _
"K" & i & ":K" & formRow - 1 & ")"
Rows(i).insert shift:=xlShiftDown
formRow = i

End If

Next

Thanks Tom - worked great. I discovered 2 more things not previously noticed...

If there is only line of text to a condition then it does not insert a
total. Example - it starts at the bottom and goes up until 2 consecutive rows
are different and inserts a blank row between them and if the next row above
is different then it inserts it there (this is correct) however, it does not
total that line it just keeps going up until it's finished.

2. How can I get the formulas to copy over to columns "AA, "AC", "AD" and
"AJ"?

Thanks again and I understand if I'm "pushing it" but I am a bit frazzled.
This macro has become quite large.

"Tom Ogilvy" wrote:

Range("a8").Select

Dim lastrow As Long, formRow As Long
Dim i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
formRow = lastrow + 1
For i = lastrow To 10 Step -1
If Cells(i, 31) < Cells(i - 1, 31) Then
Cells(formRow, "K").Formula = "=SubTotal(9," & _
"K" & i & ":K" & formRow - 1 & ")"
Rows(i).insert shift:=xlShiftDown
formRow = i

End If

Next

--
Regards,
Tom Ogilvy



"Darryl" wrote:

I have the below code in a macro that by itself runs great. I want to stop it
at row 8. The data starts in row 9 and ends when it ends - the amount of rows
always varies. I have tried using different "Do", "Do Until" but have not
been successful. Any help would be greatly appreciated.

Darryl


Range("a8").Select

Dim lastrow As Long, formRow As Long
Dim i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
formRow = lastrow + 1
For i = lastrow To 2 Step -1
If Cells(i, 31) < Cells(i - 1, 31) Then
Cells(formRow, "K").Formula = "=SubTotal(9," & _
"K" & i & ":K" & formRow - 1 & ")"
Rows(i).insert shift:=xlShiftDown
formRow = i

End If

Next

after writing the formula in K
Cells(formRow, "K").copy Cells(formRow,"AA")
cells(formRow,"K").copy Cells(formRow,"AC")

and so forth

I really can't provide any advice on you peformance problem since I don't
know how the sheet is laid out. If you can fix it, send me an example file
and an explanation at

--
Regards,
Tom Ogilvy


"Darryl" wrote:

Thanks Tom - worked great. I discovered 2 more things not previously noticed...

If there is only line of text to a condition then it does not insert a
total. Example - it starts at the bottom and goes up until 2 consecutive rows
are different and inserts a blank row between them and if the next row above
is different then it inserts it there (this is correct) however, it does not
total that line it just keeps going up until it's finished.

2. How can I get the formulas to copy over to columns "AA, "AC", "AD" and
"AJ"?

Thanks again and I understand if I'm "pushing it" but I am a bit frazzled.
This macro has become quite large.

"Tom Ogilvy" wrote:

Range("a8").Select

Dim lastrow As Long, formRow As Long
Dim i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
formRow = lastrow + 1
For i = lastrow To 10 Step -1
If Cells(i, 31) < Cells(i - 1, 31) Then
Cells(formRow, "K").Formula = "=SubTotal(9," & _
"K" & i & ":K" & formRow - 1 & ")"
Rows(i).insert shift:=xlShiftDown
formRow = i

End If

Next

--
Regards,
Tom Ogilvy



"Darryl" wrote:

I have the below code in a macro that by itself runs great. I want to stop it
at row 8. The data starts in row 9 and ends when it ends - the amount of rows
always varies. I have tried using different "Do", "Do Until" but have not
been successful. Any help would be greatly appreciated.

Darryl


Range("a8").Select

Dim lastrow As Long, formRow As Long
Dim i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
formRow = lastrow + 1
For i = lastrow To 2 Step -1
If Cells(i, 31) < Cells(i - 1, 31) Then
Cells(formRow, "K").Formula = "=SubTotal(9," & _
"K" & i & ":K" & formRow - 1 & ")"
Rows(i).insert shift:=xlShiftDown
formRow = i

End If

Next