Insert rows at change in cell value
 

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!

Insert rows at change in cell value
 

Hi

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

"Steve" wrote in message
...
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!

Insert rows at change in cell value
 

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

"Harald Staff" wrote in message
. ..
Hi

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

"Steve" wrote in message
...
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!

Insert rows at change in cell value
 

Hi Harold,

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


On Apr 29, 9:39*am, "Harald Staff" wrote:
Hi

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

"Steve" wrote in message

...



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!- Hide quoted text -

- Show quoted text -

Insert rows at change in cell value
 

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?

On Apr 29, 9:43*am, Steve wrote:
Hi Harold,

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

On Apr 29, 9:39*am, "Harald Staff" wrote:



Hi

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

"Steve" wrote in message

...

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!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Insert rows at change in cell value
 

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

On Fri, 29 Apr 2011 11:03:25 -0700 (PDT), Steve
wrote:

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?

On Apr 29, 9:43*am, Steve wrote:
Hi Harold,

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

On Apr 29, 9:39*am, "Harald Staff" wrote:



Hi

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

"Steve" wrote in message

...

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!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -