Help:

I need to compare values line by line. The scenario is like this:
If A3.value = A2.value, go to next line and do another comparison
until next line is not equal to prev line's value.

but if A3.value not equal A2.value, insert a blank line in between. I
need to loop this until I get to a line where there is no value.

the number of line is NOT pre-determined.

can anybody help?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Sub AddBlankLines()
Set rng = Range("A3")
Do
If rng.Value < rng.Offset(-1, 0).Value Then
rng.EntireRow.Insert
End If
Set rng = rng.Offset(1, 0)
Loop Until IsEmpty(rng)
End Sub

--
Regards,
Tom Ogilvy




projectside wrote in message
...
Help:

I need to compare values line by line. The scenario is like this:
If A3.value = A2.value, go to next line and do another comparison
until next line is not equal to prev line's value.

but if A3.value not equal A2.value, insert a blank line in between. I
need to loop this until I get to a line where there is no value.

the number of line is NOT pre-determined.

can anybody help?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

thanks Tom for the immediate response.

now, after the blank row is inserted, how would i add a Total to
sub-total all the same items? In other words, how do I determine the
first cell and the last cell that belong to the same group?

e.g. I have

ITEM
apple 3
apple 2
apple 5

orange 1
orange 2
orange 4



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Is there a reason you don't just use the built in SubTotal command under the
data menu. (not the subtotal worksheet function). It will do all this for
you plus provide outlining.

If you still need to code it post back.

--
Regards,
Tom Ogilvy


projectside wrote in message
...
thanks Tom for the immediate response.

now, after the blank row is inserted, how would i add a Total to
sub-total all the same items? In other words, how do I determine the
first cell and the last cell that belong to the same group?

e.g. I have

ITEM
apple 3
apple 2
apple 5

orange 1
orange 2
orange 4



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

my sample only has 2 columns. the real file has infinite columns that I
need to total for each category.

apple 3 4 1 4 4 2 4 etc..
apple 4 5 5 5 5 5 2 etc...
etc..

Total 7 9 6 9 9 7 6 etc...

Orange 5 6 7 3 5 6
Orange 6 2 5 4 4 2


thanks Tom



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Sub AddBlankLines()
Dim rng As Range, rng1 As Range
Dim col As Long
Set rng1 = Range("A2")
Set rng = Range("A3")
col = Cells(3, 256).End(xlToLeft).Column - 1
Do
If rng.Value < rng.Offset(-1, 0).Value Then
rng.EntireRow.Insert
Cells(rng.Row - 1, 2).Resize(1, col).Formula = _
"=Subtotal(9," & Range(rng1.Offset(0, 1), _
Cells(rng.Row - 2, 2)).Address(0, 0) & ")"
Set rng1 = rng
rng.Offset(-1, 0).Value = "Total " & _
rng.Offset(-2, 0).Value
rng.Offset(-1, 0).Font.Bold = True
End If
Set rng = rng.Offset(1, 0)
Loop Until IsEmpty(rng)
Set rng = rng.Offset(1, 0)
Cells(rng.Row - 1, 2).Resize(1, col).Formula = _
"=Subtotal(9," & Range(rng1.Offset(0, 1), _
Cells(rng.Row - 2, 2)).Address(0, 0) & ")"
rng.Offset(-1, 0).Value = "Total " & _
rng.Offset(-2, 0).Value
rng.Offset(-1, 0).Font.Bold = True

End Sub


--
Regards,
Tom Ogilvy

projectside wrote in message
...
yes i could have used that. but the thing is, the number of columns to
be summed varries from time to time.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/