Insert rows at change in cell value

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!
 
H

Harald Staff

Hi

In column D, use only even numbers (2-4-6-:cool: 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
 
H

Harald Staff

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

Steve

Hi Harold,

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

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?
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top