Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
add rows
What function can I use to add a row to a large spreadsheet at each change in
a value of a specified column Desperate - the manual process is overwhelming. -- REGENT |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
add rows
Hi,
Right click the sheet tab, view cod and paste this in. Changer the column as required Sub sonic() lastrow = Cells(Rows.Count, "A").End(xlUp).Row For x = lastrow To 2 Step -1 If Cells(x, 1).Value < Cells(x - 1, 1).Value Then Cells(x, 1).EntireRow.Insert End If Next End Sub Mike "REGENT" wrote: What function can I use to add a row to a large spreadsheet at each change in a value of a specified column Desperate - the manual process is overwhelming. -- REGENT |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
add rows
Can't be done with a function -- must be done with code. Paste the below
into a standard module of your workbook. Below assumes that Column A is the column that will insert a new row between change. Be sure that you first have sorted your data on Column A is this example first. 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(2).Insert 'Rows(i + 1).Insert to only Insert One Blank Row End If Next i End Sub "REGENT" wrote: What function can I use to add a row to a large spreadsheet at each change in a value of a specified column Desperate - the manual process is overwhelming. -- REGENT |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
add rows
This should do it. Please do not indicate desperate in your post. All get
the same priority. Sub addrowifchange() mc = "a" For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Insert Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "REGENT" wrote in message ... What function can I use to add a row to a large spreadsheet at each change in a value of a specified column Desperate - the manual process is overwhelming. -- REGENT |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
add rows
Hi
Try the following macro Sub insertblankrows() Dim i As Long, lr As Long lr = Cells(Rows.Count, "A").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, 1) < Cells(i - 1, 1) Then Rows(i).Insert i = i - 1 End If Next i End Sub Copy the Code above Alt+F11 to invoke the VB Editor InsertModule Paste code into white pane that appears Alt+F11 to return to Excel To use Select sheet containing the PT's Highlight the macro name Run -- Regards Roger Govier "REGENT" wrote in message ... What function can I use to add a row to a large spreadsheet at each change in a value of a specified column Desperate - the manual process is overwhelming. -- REGENT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
How do I view the maximum rows in Excel 2007 (Million Rows)? | Excel Discussion (Misc queries) | |||
Excel 2003 -Rows hidden. Scrolling unhides rows ! How do I stop th | Excel Discussion (Misc queries) | |||
Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions |