Insert rows at change in cell value

  • Thread starter Thread starter Steve
  • Start date Start date
S

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!
 
Hi Harold,

Thanks for the response. Unfortunately the grouping values are text
strings...basically categories.
 
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?
 
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
 
Back
Top