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

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

- Macro to create a new worksheet at each change in data
- Macro: Inserting multiple rows after every change in column
- Macro: Inserting multiple rows after every change in x column
- Insert A blank Row Everytime the date changes in column B
- Insert lines at each change in value
- Insert empty cell after each change in list
- Insert a blank row after change in data
- Macro to add rows at a change in data
- Insert Row At Each Change In Column
- Macro to insert rows at each change in column, create a new header row and subtotal
- Insert Blank cells (partial row) at each change in value of cells in a column
- Insert rows at a change in a column
- Enter row after change in name
- Insert and FORMAT new row with each change in cell value
- How do you insert " before and after each sentence in every cell?
- Inserting sub-total after change in values in three columns
- How do you insert " before and after each sentence in every cell?
- Find changes in data and insert blank row
- Increase Aphanumeric Number For Each Change In Data
- I need a line insert after a change in column H (VBA)
- Page breaks after Subtotal
- Insert row before the found word
- Page Break Automation
- New Tab For Each Change in Value

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

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

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

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.

list of data. Can I set it up to automatically insert a line at each change

(like subtotal, but without the subtotal data)?

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

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.

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 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:Any and all help is appreciated.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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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 SubIf 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.

Building on a previously created thread, found here:

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

How would I change this code:

VB: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?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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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!

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.

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

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

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

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

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.

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

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’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

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 IfI want to insert rows before the word "grand total" and copy paste some data before this word, thanks for the quicker response

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

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!