Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Insert Rows Before & After Each Change In Data

Hello, I'm relatively new to VBA...

I need to loop down a column of data containing branch numbers and whenever there is a new branch, I need to insert a row both before and after the last instance of the previous brance. For example:

If the first three rows in the column contain branch 0001 and the forth row starts with branch 0002, I need to insert a row both before and after the 0001 in row three...

Can anyone please help?

Thanks,

Stephen


Post your answer or comment

comments powered by Disqus
Hi,
am hoping someone can help with this? I have been searching for a solution for a long time now.

What I need to come up with is a macro that will create a new worksheet at each change in Data.

Ie)
I have a worksheet full of data, and at each change in the data of column A I would like the a new worksheet created within the same workbook. Something to the effect of:

Column A
bill
bill
bill
Jack
Jack
Dave
Dave
Dave
Dave

The macro would take the above data, and create three additional worksheets. 1 with all the rows related to bill, one with the rows related to Jack, and one for Dave. What I am finding difficult to account for is the fact the data in column A can change. For example, in the above list "bill" appears 3 times. But in actuality "bill" could have any number of rows of data.

anyone have any ideas?

thanks

Hi, first I want to thank you for your help and this website.

My question is how do I insert multiple rows after every change in column?

I am able to insert one row with either of the following; however, I can't figure out to insert 4 rows:

Number_of_rows = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select
Do Until Selection.Row = Number_of_rows + 2
If Selection.Value <> Selection.Offset(-1, 0).Value Then
Selection.EntireRow.Insert
Number_of_rows = Number_of_rows + 2
Selection.Offset(2, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop

OR

'Insert row after each change in FUND CODE'
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Cells(lRow, "B") <> Cells(lRow - 1, "B") Then Rows(lRow).EntireRow.Insert
Next lRow

Thank you for you help,
Mike

Hi, first I want to thank you for your help and this website.

My question is how do I insert multiple rows after every change in column?

I am able to insert one row with either of the following; however, I can't figure out to insert 4 rows:

Number_of_rows = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select
Do Until Selection.Row = Number_of_rows + 2
If Selection.Value <> Selection.Offset(-1, 0).Value Then
Selection.EntireRow.Insert
Number_of_rows = Number_of_rows + 2
Selection.Offset(2, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop

OR 

'Insert row after each change in FUND CODE'
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Cells(lRow, "B") <> Cells(lRow - 1, "B") Then Rows(lRow).EntireRow.Insert
Next lRow

Thank you for you help,
Mike

I have an xls sheet with 500 rows and 4 columns. In column B is the date for
that particular record.
Is there a way that I can have xls insert a blank row everytime it
encounters a change in date in colum B. For example I may have 10 records
with the date 01-Sep-06, then after those 10 records that date wil be
02-Sep-06 and that may be 5 rows.
I want a blank row automatically inserted after each change in date.

I need to insert a line at each change in "property Number" in a very long
list of data. Can I set it up to automatically insert a line at each change
(like subtotal, but without the subtotal data)?

Hi,

This must be easy but I can't put my finger on it, I have a list of cells that goes like this:

A
A
A
B
B
C
D
D
D
D

The order is random and dynamic,I would like in the column next to it to have the same list but with one empty space between each change, just like:

A
A
A

B
B

C

D
D
D
D

You get the idea, so any suggestion, any help is greatly appreciated!

Cheers

Thanks Max, that did the trick. I was concerned because I actually needed
the function to START at row 10 - not end at row ten. But perhaps I don't
understand the coding language because it did start work - starting at row 10
where my data starts. Thanks again.

"Max" wrote:

> Came across a sub below by a poster in .programming
> Modified a little to end at row 10 to suit.
> Try it on a spare copy ..
>
> Sub InsertRow_At_Change()
> Dim i As Long
> With Application
> .Calculation = xlManual
> .ScreenUpdating = False
> End With
> For i = Cells(Rows.Count, 2).End(xlUp).Row To 10 Step -1
> If Cells(i - 1, 2) Cells(i, 2) Then _
> Cells(i, 1).Resize(1, 1).EntireRow.Insert
> Next i
> With Application
> .Calculation = xlAutomatic
> .ScreenUpdating = True
> End With
> End Sub
>
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Genghis2k3" wrote:
> > I have a spreadsheet with 300 rows of data and I need a macro that will
> > insert a blank row after a change in the data in column B. The data starts
> > in row 10. Thanks for your help.

Sample data :

COLORADO 014C 2391942 3528600 141
172 724899 1067108 40
1797 546812 820255 34
1998 416032 626383 28
2216 806535 1176044 44
237 1872461 2710125 104
2477 168605 253895 10
2830 1016305 1527912 64
3262 396654 557467 20
3524 1261749 1913467 79
3525 1704009 2442987 99
3609 614421 914285 33
3641 1204536 1760984 73
3704 1914318 2761913 109
3748 913416 1333302 54
3934 1355085 2068155 85
3938 857785 1273634 57
COLORADO TOTAL 18165564 26736516 1074
36331128 53473032 2148

Other info:

1. I used the subtotal command to get the last line of data. Note that the
row above it is the subtotal already in the spreadsheet.

2. In column a, there is a number that reflects the state in column B

3. Columns C- F have data that gets subtotaled.

How do I create a macro that:

1. inserts a row above and below where each subtotal occurs. Keep in mind it
would be a change in Column A (would prefer B).

2. After inserting the rows , I then need to delete the rows that has the
State Total already in the spreadsheet

3. Or better yet: lookup value in column a, preplace blank in column b with
dat from lookup table. At change in column b, insert row. Needs to be done in
this order, as if this is done before lookup/replace, I will have a break
from "state" and state total"

Thanks..

I am trying to achieve a row insert based on matching criteria. I need to check a column of text values, and each time the text value changes, copy cells (a1:c1) and insert copied selection to the row before text change.

I am only concerned with the text in the first column. For example, I have a column with sometext in each row, when the row changes to somenewtext, I want to copy the header information and insert into row before the text changed.

Header1|Header2|Header3|
sometext
sometext
sometext
somenewtext

Searching the forums, I found conditional page breaks http://www.ozgrid.com/Excel/excel-co...age-breaks.htm and tried to adapt the code, but have been unsuccessful in getting it to work for my needs. I have tried the following, but cant figure out how to insert the rows in the correct place. Here is what I tried.

	VB:
	
 cln() 
    Dim myRange As Range 
    Dim rngCell As Range 
     
    With Worksheets("pendingRpt") 
        Set myRange = .Range(.Range("A1"), .Range("A65536").End(xlUp)) 
        For Each rngCell In myRange 
            If rngCell.Text  rngCell.Offset(1, 0).Text Then 
                rngCell.Select 
                With Selection.Interior 
                    Range("A1:C1").Select 
                    Selection.Copy 
                    Selection.Insert shift:=xlDown 
                End With 
            End If 
        Next 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any and all help is appreciated.

Hi,

I am trying to determine how this can be done. I want to quickly format my data using a macro. Previously I have done it manually and it has just been taking too long.

I've put together a sample spreadsheet below of my data. On one tab I have the raw data that I receive, and on the second tab I have what I would like my output to look like...

Sample Subtotal.xlsx

Normally when I do this manually, I first insert 2 blank rows at each change in column A and then copy the row header from the top of the spreadsheet to each of the second blank rows that I have inserted.

Finally, I use the Data>Subtotal function to subtotal each table that I have created for every change in column D.

So far I have been able to figure out how to automate the first 2 steps using the following VBA code:


	VB:
	
 AddRows_ValueChange() 
    Dim r As Long 
    For r = Cells(Rows.Count, 1).End(xlUp).Row To 4 Step -1 
        If Cells(r, 1)  Cells(r - 1, 1) Then Rows(r).Insert Shift:=xlDown 
        If Cells(r, 1) = vbNullString Then 
            Range("A1:E1").Copy Destination:=Cells(r, 1) 
        End If 
    Next r 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I cannot however figure out how to subtotal everything to match my manual output, other than doing it one by one.
The purpose if doing this is to then paste each table Ive created (i.e. AA, BB, CC...) into another file as back up.

I would appreciate any assistance from you experts..thank you in advance.

Hi,

Building on a previously created thread, found here:

http://www.ozgrid.com/forum/showthre...ank+row+change

How would I change this code:


	VB:
	
 Macro1() 
     
    Dim strMyCol As String 
    Dim lngRowStart As Long, _ 
    lngRowEnd As Long 
    Dim intNoOfRowsToInsert As Integer 
     
    strMyCol = "G" 'Column containing relevant data.  Change to suit.
    lngRowStart = 1 'Row number where the data starts.  Change to suit.
    intNoOfRowsToInsert = 1 'Number of rows between groups.  Change to suit.
    lngRowEnd = Cells(Cells.Rows.Count, strMyCol).End(xlUp).Row 
     
    Application.ScreenUpdating = False 
     
    For lngActiveRowNum = lngRowEnd To lngRowStart Step -1 
        If Cells(lngActiveRowNum, strMyCol)  _ 
        Cells(lngActiveRowNum - 1, strMyCol) And lngActiveRowNum > lngRowStart Then 
            Rows(lngActiveRowNum).Resize(intNoOfRowsToInsert, 1).EntireRow.Insert shift:=xlDown 
        End If 
    Next lngActiveRowNum 
     
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
to add blank cells in only certain columns (for instance, columns A through G) instead of adding an entire row at each change in the value of cells in column G?

The reason for this is that I have another data set in columns L through U that should stay intact. The two data sets are being compared.

I started a new thread as I did not want to hijack and my question was (marginally) different.

Thanks for your help!

I am new to these forums and this is my first post.
Oh, and I'm not really all that familiar with a lot of things in Excel. I do know how to make a macro, which this question is to help with, but I kinda skipped a lot of learning in between.

I am wanting a shortcut that will insert a new row after each change in column A. I don't think Subtotals is the answer because after I enter the row to seperate them I will want to enter another row and seperate again by column F when there is a change that is not a blank row.

hello,

I have a macro which will enter 2 rows after each change in name in column G. Two questions - this works using a for / next loop but it takes quite a while to run. Also, I'm sure there's a better way to insert 2 rows rather than writing the line of code twice. Any one have any ideas to improve?

many thanks for your help!!

Sub Insert_rows()

Dim RowNumber As Integer
Dim Counter As Integer
Dim i As Integer

Rows("2:3").Insert Shift:=xlDown
Rows("2:3").Interior.ColorIndex = xlNone

RowNumber = ActiveSheet.UsedRange.Cells.Rows.Count

Counter = 5

For i = 5 To RowNumber

If Cells(Counter, 7) Cells(Counter - 1, 7) Then
Rows(Counter).Insert Shift:=x1Down
Rows(Counter).Insert Shift:=x1Down

Counter = Counter + 2

End If

Counter = Counter + 1

Next i

End Sub

All,
Thanks for everyone sharing their knowledge - I have learned SO much...! Now... taking the first step - admitting you have a problem...!

I'm running thru a Range - and for each change in value - inserting a new row. OK - no problem.

However, I would like this newly inserted row to be formatted in some way, then have the routine continue on it's merry way until finding another row that needs to be inserted.

For example - maybe I want to change the row height, or change the Interior color from Columns A thru Z for example - or any other formatting desired.

I've reached my limit on trying to accomplish this - so now reaching out...!!!

small section of relevent code:
If CILineNameRange.Cells(x, 1).Offset(-1, 0).Value CILineNameRange.Cells(x, 1).Value Then

CILineNameRange.Cells(x, 1).EntireRow.Insert shift:=xlDown

End If

' the above inserts a row - but I would like to insert a colored row from say... colums A thru Z,
' and maybe change the row height, or whatever other formatting desired
' - such as
' .ColorIndex = 15
' .Pattern = xlSolid
' .RowHeight = 4.00

Many thanks in advance!
Mike

Hi,

I am no wizard when it comes to excel so any help would be great.

How do you insert " before and after each sentence in every cell?

I have 4200 cells with text that needs to have " before and after each
sentence.

--
zooloo
------------------------------------------------------------------------
zooloo's Profile: http://www.excelforum.com/member.php...o&userid=30312
View this thread: http://www.excelforum.com/showthread...hreadid=499780

In the spreadsheet, I sort by column A, then B, then C. I then insert a blank row after each change in value in those columns. I would like to add subtotals to each section. This is what I have tried. I get a "Method 'Range' of object '_Global' failed."

Please help.

Private Sub cmdUpdate_Click()


Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
            
            

Dim i As Long
        
      
                For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
                If Cells(i, "A") <> Cells(i - 1, "A") _
                And Cells(i, "A") <> "" _
                And Cells(i - 1, "A") <> "" Then
                Rows(i).Insert
                Rows(i).Select
                Selection.Interior.ColorIndex = xlNone
                
                
    
                Range(Cells(i, "E")).Select
                ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)"
                Range(Cells(i, "F")).Select
                ActiveCell.FormulaR1C1 = "=SUM(RC[-1])"
                
                
                
            End If
            
            
        Next
        
        For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
            If Cells(i, "B") <> Cells(i - 1, "B") _
                And Cells(i, "B") <> "" _
                And Cells(i - 1, "B") <> "" Then
                Rows(i).Insert
                Rows(i).Select
                Selection.Interior.ColorIndex = xlNone
                
                Range(Cells(i, "E")).Select
                ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)"
                Range(Cells(i, "F")).Select
                ActiveCell.FormulaR1C1 = "=SUM(RC[-1])"
                
            End If
        Next

        For i = Cells(Rows.Count, "C").End(xlUp).Row To 2 Step -1
            If Cells(i, "C") <> Cells(i - 1, "C") _
                And Cells(i, "C") <> "" _
                And Cells(i - 1, "C") <> "" Then
                Rows(i).Insert
                Rows(i).Select
                Selection.Interior.ColorIndex = xlNone
                
                Range(Cells(i, "E")).Select
                ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)"
                Range(Cells(i, "F")).Select
                ActiveCell.FormulaR1C1 = "=SUM(RC[-1])"
                
            End If
        Next
    

End Sub


Hi,

I am no wizard when it comes to excel so any help would be great.

How do you insert " before and after each sentence in every cell?

I have 4200 cells with text that needs to have " before and after each sentence.

Hi, Can someone help me with a VBA macro that is similar to the subtotals function but without the subtotals? Basically I want the macro to look at column A and say, for each change in column A insert a blank row… I don’t need to worry about the sorting, I just would like to know how I can tell a spreadsheet to look at a column find changes in that column and just insert a blank row. On the second page of the attached spreadsheet is an example of the desired output… I will probably use this for many different spreadsheets so the simpler and more versatile the better, ie I can change the column I want the macro to look at. thanks for your help!

Hello Folks,

Happy New To All!

I have many data in a worksheet named "Table" with "Item" in column A and "Reference" in column B. For each change of item, I need to assign a reference number with a prefix. Below is a sample of the data.

Column A Column B
Item Reference
Orange R9
Orange R9
Orange R9
Apple R10
Apple R10
Apple R10
Mango R11
Mango R11

The starting reference number is not fixed. It could start from 8 or 10.

Thought it would be possible to automate this process using vba.

Would be grateful for any advice.

Thanks and regards,
Gon

Hey people, do any of you know a solution to the following problem?

I currently have a macro that does several thing, i want to add another task.
I need a row inserted everytime there is a change in column H from "0" to "F" only. The infromation will always start in row 8 but the last row is not always the same, it will be different every time.

ABCDEFGHI112345678223456783#N/A#N/A#N/A#N/A#N/A#N/A#N/A45B=Bi-WeeklyID type 2ID Type 308 GAPamount due6HCCompanyS= Semi-MonthlyCO#NAMEFORMER (F) or Current (0)78546WalreBbr55a234amy01009524WalreSn32m56daniel05001041WalreS589653jode0100011945WalreBef343456caryF2001212WalreSg5365M5665mikeF40013Grand Total2,200.00

Thank you.

I appreciate your expertise that is so willingly shared!

I’m having a problem with the Data/Subtotals/Sum function not inserting page breaks after each Vend #. It does not work in my original spreadsheet but does when I copy and paste on sheet of data to a blank workbook. I’m doing the function in exactly the same way...(in the Subtotal dialog box, I've choosen "At each change in Vend #, Use Function Sum, Add Subtotal to Billback, and checked Page Break between Groups.) The new spreadsheet does it right - adds totals and inserts page breaks after each change in Vend # but the original doesn’t. Could anyone help me figure out how to fix this? The original file has 8 sheets, two of which have 31000 rows of data, the others having 300-1800 rows. Thanks for your help. Lynne

Hello I have a problem regarding of inserting row before the word if my vba code found that code. The thing is it only insert row after the word.
Dim Cell As Range
  Dim Match As Range
  Dim Rng As Range
  Dim Wks As Worksheet
  Set Wks = Worksheets("VII. AP BAS G11")
    
    Set Rng = Wks.Range("A11").CurrentRegion
    Set Rng = Intersect(Rng, Rng.Offset(1, 0))
    
    Set Match = Rng.Cells.Find("Grand Total", , xlValues, xlWhole, xlByRows, xlPrevious, False)
    
    If Not Match Is Nothing Then
       Set Match = Match.Offset(1, 0)
       Match.Resize(RowSize:=2).EntireRow.Insert Shift:=xlDown
       
      
    End If
I want to insert rows before the word "grand total" and copy paste some data before this word, thanks for the quicker response

I tried Gord D.'s "clunker".
Couldn't get it to work, even with help from the group.
Then I found the following, apparently originally from Frank Kabel.
Works great, EXCEPT, it also puts a page break under the header.
Can someone tell me how to get the following to ignore header rows.
If I can specify (within the module), the number of header rows,
this macro would be very versatile. (for many people)
Just specify how many header rows there are,
and which column is to be searched......
and Bob's your uncle.

Sub AAAInsertBreak()
' AAAInsertBreak Macro
' Insert Page Break after each change of
' Data in Column B
' From Frank Kabel, Germany

' I added the following reset
ActiveSheet.ResetAllPageBreaks

Dim lastrow As Long
Dim row_index As Long

'All the "B"'s were "A"'s, originally

lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "B").Value <> _
Cells(row_index + 1, "B").Value Then
ActiveSheet.HPageBreaks.Add Before:= _
Cells(row_index + 1, "B")
End If
Next

'I added the Message Box
MsgBox "COMPLETE!"

End Sub

Hi Everyone,
I'm working on sorting a worksheet with a lot of data (30,000 rows, 20 columns) and I need to take this data and create a new tab for each change in value for column L. I've done this work manually by filtering for each of these changes in value and copying into a new sheet, but I'm wondering if there is an easier way. Any ideas? TIA!


No luck finding an answer? You could always try Google.