Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a row when theres change in a row.
Hi all,
Does anyone knows how to insert a row automatically when the value within a column changes? Ie. Column A is list of names, sort by A to Z, (where there are values in other columns too) So you may have a column looks like: Amanda Amanda Amanda Becca Becca Charlotte Elle Elle Elle etc Is there a way to automatically insert a row after changes to make the following: Amanda Amanda Amanda Becca Becca Charlotte Elle Elle Elle Thanks. Elaine. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a row when theres change in a row.
Paste this into a standard module:
Sub InsertRow_A_Chg() Dim Lrow As Long, vcurrent As String, i As Long '// find last used cell in Column A Lrow = Cells(Rows.Count, "A").End(xlUp).Row '// get the value of that cell in Column A (column 1) vcurrent = Cells(Lrow, 1).Value '// rows are inserted by looping from bottom up For i = Lrow To 2 Step -1 If Cells(i, 1).Value < vcurrent Then vcurrent = Cells(i, 1).Value Rows(i + 1).Resize(1).Insert 'NUMBER of Rows to Insert Line --Rows(i + 1).Insert to only Insert One Blank Row End If Next i End Sub Hope it helps, Jim May "elaine" wrote: Hi all, Does anyone knows how to insert a row automatically when the value within a column changes? Ie. Column A is list of names, sort by A to Z, (where there are values in other columns too) So you may have a column looks like: Amanda Amanda Amanda Becca Becca Charlotte Elle Elle Elle etc Is there a way to automatically insert a row after changes to make the following: Amanda Amanda Amanda Becca Becca Charlotte Elle Elle Elle Thanks. Elaine. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a row when theres change in a row.
Hi JM,
does it mean that I could choose any column buy change 'A' to what ever column its on and the code will still work? Thanks E. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a row when theres change in a row.
Yeah, Say you want Column D
Sub InsertRow_D_Chg() Dim Lrow As Long, vcurrent As String, i As Long '// find last used cell in Column D Lrow = Cells(Rows.Count, "D").End(xlUp).Row '// get the value of that cell in Column D (column 1) vcurrent = Cells(Lrow, 4).Value '// rows are inserted by looping from bottom up For i = Lrow To 2 Step -1 If Cells(i, 4).Value < vcurrent Then vcurrent = Cells(i, 4).Value Rows(i + 1).Resize(1).Insert 'NUMBER of Rows to Insert Line '--Rows(i + 1).Insert to only Insert One Blank Row End If Next i End Sub "elaine" wrote: Hi JM, does it mean that I could choose any column buy change 'A' to what ever column its on and the code will still work? Thanks E. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a row when theres change in a row.
You may want to add headers, sort your data (if you want) and then use
data|subtotals to get that inserted line. And you'll end up with counts or sums of anything field you want, too. Along with the outlining symbols to the left that allow you to hide/show details. elaine wrote: Hi all, Does anyone knows how to insert a row automatically when the value within a column changes? Ie. Column A is list of names, sort by A to Z, (where there are values in other columns too) So you may have a column looks like: Amanda Amanda Amanda Becca Becca Charlotte Elle Elle Elle etc Is there a way to automatically insert a row after changes to make the following: Amanda Amanda Amanda Becca Becca Charlotte Elle Elle Elle Thanks. Elaine. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a row when theres change in a row.
It works perfectly if I only have a few rows of data, but it seems to
take a long time/crashing when I use this code on a 30,000 rows data.... How long does it take the code to work for 30k rows??? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a row when theres change in a row.
It takes a long time!
Try turning calculation to manual (tools|Option|calculation tab) to see if that helps. elaine wrote: It works perfectly if I only have a few rows of data, but it seems to take a long time/crashing when I use this code on a 30,000 rows data.... How long does it take the code to work for 30k rows??? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert row at change macro - how to change it. | Excel Discussion (Misc queries) | |||
Insert a day on change of other cell | Excel Worksheet Functions | |||
Insert lines at each change in value | Excel Discussion (Misc queries) | |||
I can't add a row How can I change it to let me insert a row | Excel Discussion (Misc queries) | |||
Insert a row after each account no change | Excel Programming |