Insert rows at change in cell value

[Steve]

Hi all. I have a worksheet with a header row in row 1. In column D I
have "grouping values" that we often change. The worksheet is sorted
based on the value in column D, keeping like groupings together. I
also have manually inserted a row in between each section and colored
it grey.

So as I change a value in D, I have to resort the sheet, which messes
up my blank grey row in between sections. Is there a way to do this
via code? ie, sort the sheet based on column C (primary), then by
column D (secondary), identify which row the grouping value in D
changes, insert a blank row and color it grey (color the range from
column A thru column T)?

Thanks for any help!

 

[Harald Staff]

Hi

In column D, use only even numbers (2-4-6- as your grouping values, and
put a series of odd numbers (3-5-7-9-) in D in the grey rows. Color those
numbers grey if you don't want to see them.

HTH. Best wishes Harald

 

[Harald Staff]

Second thought, you may be using duplicate grouping values... you may need
code then.

 

[Steve]

Hi Harold,

Thanks for the response. Unfortunately the grouping values are text
strings...basically categories.

 

[Steve]

After some thought, this project is not worth the time and effort to
figure out the sorting thing. BUT, is there still a way to identify
where the value in column D changes from the previous row, and insert
a grey row from A thru T?

 

[Gord Dibben]

Sub Insert_Gray_Row_At_Change()
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 2 Step -1
If Cells(X, 1).Value <> Cells(X - 1, 1).Value Then
If Cells(X, 1).Value <> "" Then
If Cells(X - 1, 1).Value <> "" Then
Cells(X, 1).EntireRow.Insert Shift:=xlDown
Cells(X, 1).EntireRow.Interior.ColorIndex = 15
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP