Looking for a way to insert row at value change in a column

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
I have a weekly report I work on and to this point I've had to do this manually. Let's say that A1 has a header and below that are the names of states with duplicates having been sorted to fall together so that it would look like the following:

State
Virginia
Virginia
Virginia
Washinton
Washinton
Washington
Wyoming
Wyoming

I would like to be able to insert a row above each new item in column A. In other words at each change in value such that the end result would be like this:

State

Virginia
Virginia
Virginia

Washinton
Washinton
Washington

Wyoming
Wyoming
______________________

Any ideas?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi:

You might try this bit of code fromr Norie

Sub InsertRows()
Dim rng As Range
Set rng = Range("A1")
While rng.Value <> ""
If rng.Value <> rng.Offset(1).Value Then
rng.Offset(1).EntireRow.Insert
Set rng = rng.Offset(1)
End If
Set rng = rng.Offset(1)
Wend
End Sub



plettieri
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
Awesome ... works like a charm ... though being a relative newbie I don't necessarily understand the code enough to know why.
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi:

A way of learning some of the code is to use the macro recorder and follow along with the code that is written for you. This will help in understanding some of the basics.

pll
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello,

while the posted code is rather "intuitive" - moving down row by row - it is not "computerlike"
when coding we should try to think more globally - f.i. in terms of "entirecolumn-formulas"

this can speed up the process considerably
Code:
Option Explicit 

Sub insert_rows_on_each_change() 
'Erik Van Geit 
'080628 

'EXAMPLE 
'CC = 3, FR = 2, NR = 2 
'START WITH 
'a1  b1  header  d1 
'a2  b2  A   d2 
'a3  b3  A   d3 
'a4  b4  B   d4 
'a5  b5  C   d5 
'a6  b6  C   d6 
'RESULT 
'a1  b1  header  d1 
'a2  b2  A   d2 
'a3  b3  A   d3 
' 
' 
'a4  b4  B   d4 
' 
' 
'a5  b5  C   d5 
'a6  b6  C   d6 

Dim rng As Range 
Dim LR As Long              'Last Row 
Dim CC As Long 
Dim FR As Long 
Dim NR As Long 

'***** EDIT the following lines **** 
CC = 1        'Check this Column 
FR = 2        'First Row with data: MINIMUM = 2 
NR = 1        'Number of Rows to insert 
'***** END EDIT **** 

Application.ScreenUpdating = False 

LR = Cells(Rows.Count, CC).End(xlUp).Row 
Columns(CC).EntireColumn.Insert 

Set rng = Range(Cells(FR + 1, CC), Cells(LR, CC)) 

Cells(FR, CC) = 1 

    With rng 
    .FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C,R[-1]C+1)" 
    .Value = .Value 
        With .Offset(.Rows.Count, 0) 
        .Cells(1, 1).Value = 1 
            With .Resize(.Cells(1, 1).Offset(-1, 0) - 1, 1) 
            .DataSeries Rowcol:=xlColumns, Type:=xlLinear, step:=1 
            .Copy .Resize(NR * .Rows.Count, 1) 
            End With 
        End With 
    LR = Cells(Rows.Count, CC).End(xlUp).Row 
    Range(Cells(FR, CC), Cells(LR, CC)).EntireRow.Sort Key1:=.Cells(1, 1) 
    End With 

Columns(CC).EntireColumn.Delete 

Application.ScreenUpdating = True 

End Sub

stepping through the code usinf cuntion key F8, while having a look at your sheet, you will learn a lot about this kinda processes

another (code)approach would be
insert SUBTOTALS
then delete the contents of inserted lines


kind regards,
Erik

PS: nice avatar ;)
 

Forum statistics

Threads
1,141,060
Messages
5,704,042
Members
421,324
Latest member
Devo182

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top