Insert a Row and then Sum
 

I have a problem. I have a large set of data whose first column downloads a
manufactured part that is tied to a list of attributes. For example the
first 10 rows in column 1 are all the same number, e.g. 12345 and then the
each row in column 2, 1 thru 10 is a different numbered attribute such as Row
1 Column 2 is 890 and Row 2 Column 2 is 899, etc. After the initial 10 rows
it starts with another manufacturers part that might go for 20 rows, etc. I
want to insert two blank rows and sum the 10 rows in Column 2, and then the
next X rows for the next part number. I thought I had it figured out with
the below macro, but for some reason after the rows are inserted I run the
sum macro and it stops at row 4,460 even though I have more data to sum. I
scrubbed the data to see if it is an anomolly with the row or the data in
that cell but there doesn't seem to be a problem with it. Here are my
macros: Can someone help me out or explain why it's stopping at row 4,460?

Sub SumAndSeparate()
StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is

i = StartRow + 1
While Cells(i, DataColumn) < ""
If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
Cells(i, DataColumn).EntireRow.Insert ' a 2nd blank row added
i = i + 2
End If
i = i + 1
Wend

End Sub
Sub insert_sum_values()
Dim sum_of_range, tmp

For i = 1 To 32

If i = 1 Or i = 11 Or i = 16 Or i = 17 Or i = 18 Or i = 19 Or i = 20
Or i = 21 Or i = 22 Or i = 23 Or i = 24 Or i = 25 Or i = 26 Or i = 27 Or i =
28 Or i = 29 Or i = 30 Or i = 31 Or 32 Then
'column numbers where sums required
Cells(3, i).Select
'first cell at top of range to be summed

Do
Range(ActiveCell, ActiveCell.End(xlDown)).Select
tmp = ActiveCell.Value
If tmp < "" Then
sum_of_range = "=SUM(" & Selection.Address & ")"
ActiveCell.End(xlDown).Offset(1, 0).Value = sum_of_range
ActiveCell.End(xlDown).Offset(2, 0).Select
Else
sum_of_range = ""
End If
Loop Until sum_of_range = ""
End If
Next i
End Sub

Insert a Row and then Sum
 

When inserting/deleting rows best to go from the bottom up.
Also, why not exclude the columns instead of enumerating all
Also, why loop to sum?
Perhaps SUBTOTALs would work for you??? Hard to say without seeing.
Try again, and if all else fails
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Buyer" wrote in message
...
I have a problem. I have a large set of data whose first column downloads
a
manufactured part that is tied to a list of attributes. For example the
first 10 rows in column 1 are all the same number, e.g. 12345 and then the
each row in column 2, 1 thru 10 is a different numbered attribute such as
Row
1 Column 2 is 890 and Row 2 Column 2 is 899, etc. After the initial 10
rows
it starts with another manufacturers part that might go for 20 rows, etc.
I
want to insert two blank rows and sum the 10 rows in Column 2, and then
the
next X rows for the next part number. I thought I had it figured out with
the below macro, but for some reason after the rows are inserted I run the
sum macro and it stops at row 4,460 even though I have more data to sum.
I
scrubbed the data to see if it is an anomolly with the row or the data in
that cell but there doesn't seem to be a problem with it. Here are my
macros: Can someone help me out or explain why it's stopping at row
4,460?

Sub SumAndSeparate()
StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is

i = StartRow + 1
While Cells(i, DataColumn) < ""
If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
Cells(i, DataColumn).EntireRow.Insert ' a 2nd blank row added
i = i + 2
End If
i = i + 1
Wend

End Sub
Sub insert_sum_values()
Dim sum_of_range, tmp

For i = 1 To 32

If i = 1 Or i = 11 Or i = 16 Or i = 17 Or i = 18 Or i = 19 Or i =
20
Or i = 21 Or i = 22 Or i = 23 Or i = 24 Or i = 25 Or i = 26 Or i = 27 Or i
=
28 Or i = 29 Or i = 30 Or i = 31 Or 32 Then
'column numbers where sums required
Cells(3, i).Select
'first cell at top of range to be summed

Do
Range(ActiveCell, ActiveCell.End(xlDown)).Select
tmp = ActiveCell.Value
If tmp < "" Then
sum_of_range = "=SUM(" & Selection.Address & ")"
ActiveCell.End(xlDown).Offset(1, 0).Value =
sum_of_range
ActiveCell.End(xlDown).Offset(2, 0).Select
Else
sum_of_range = ""
End If
Loop Until sum_of_range = ""
End If
Next i
End Sub