Excel 2002: Can I do conditional editing ?

[Mr. Low]

Hello,

I have the folowing table:


1 Code Date Amount
2 L21 XXX XXXXX
3 L21
4 U54 <--- insert a row above when the code
chanes
5 Y63
6 Y63
7 Y63
8
1499 B16
1500 P58

Is there a way for me to insert a row automatically above it code changes ?

Thanks

Low

 

[Mike H]

Hi,

I assume that's column A with the codes in. Right click your sheet tab, view
code and paste this in and run it.

Sub cutaneous()
Dim MyRange
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = lastrow To 2 Step -1
If Cells(x, 1).Value <> Cells(x - 1, 1).Value Then
Rows(x).EntireRow.Insert
End If
Next
End Sub

Mike

 

[Dave Peterson]

You may want to sort your data by the code and then use Data|Subtotals.

You'll get that extra line inserted along with totals/averages/counts (whatever
you choose).

 

[Shane Devenshire]

Hi,

It is also possible to do this via a PivotTable, however I think Mike's code
or a modification of it would be better:

Sub cutaneous()
Dim x As Long
Application.ScreenUpdating = False
For x = [A65536].End(xlUp).Row To 2 Step -1
If Cells(x, 1) <> Cells(x - 1, 1) Then
Rows(x).EntireRow.Insert
End If
Next x
End Sub

Cheers,
Shane