how do i compare values line by line??

[projectside]

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?

 

[Tom Ogilvy]

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

 

[projectside]

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

 

[Tom Ogilvy]

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.

 

[projectside]

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

 

[Tom Ogilvy]

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