Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how do I insert a blank row when a value changes, automatically
When I have a worksheet that is sorted on several columns, how can I set up
an action that will automatically insert a blank row between rows that have a change to a given column's value, from the row just above. Sample Value's 12 12 12 blank row 13 13 13 blank row 14 14 14 thank you |
#2
|
|||
|
|||
how do I insert a blank row when a value changes, automatically
The following VBA procedure should do your work.
Sub InsertRows() StartRow = 2 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 While Cells(i, DataColumn) < "" If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert i = i + 1 End If i = i + 1 Wend End Sub To run it: Alt+F11 for the VB editor menu command Insert | Module Paste the code From Excel: Alt+F8 HTH Kostis Vezerides |
#3
|
|||
|
|||
how do I insert a blank row when a value changes, automaticall
Perfect - thank you
"vezerid" wrote: The following VBA procedure should do your work. Sub InsertRows() StartRow = 2 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 While Cells(i, DataColumn) < "" If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert i = i + 1 End If i = i + 1 Wend End Sub To run it: Alt+F11 for the VB editor menu command Insert | Module Paste the code From Excel: Alt+F8 HTH Kostis Vezerides |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I insert a blank row when a value changes, automaticall
Hi Vez'
I found this very helpful - thank you for posting it. Can I be cheeky and ask for a little something extra to this please? On the extra row that is being created - is it possible to sum the cells (of certain columns) above please? I've used your VBA below to put an extra row in after every change in team name within my organisation (column A). Some teams have 10 people in, while others have 100+ so putting this blank row automatically is very useful. I then go through the sheet and manually sum the columns (T, U and BK) required for each team. I've tried to amend the code myself but I can't seem to get it to work properly, as some of the 'sums' are for the 10 rows above and then some are for 100. Is it possible to amend your code to include the sums for these 3 colums please? Thanks in advance, AW "vezerid" wrote: The following VBA procedure should do your work. Sub InsertRows() StartRow = 2 'Change the 2 to the row actual data start DataColumn = 1 'Change the 1 to the column where your data is i = StartRow + 1 While Cells(i, DataColumn) < "" If Cells(i, DataColumn) < Cells(i - 1, DataColumn) Then Cells(i, DataColumn).EntireRow.Insert i = i + 1 End If i = i + 1 Wend End Sub To run it: Alt+F11 for the VB editor menu command Insert | Module Paste the code From Excel: Alt+F8 HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to insert blank lines | Excel Discussion (Misc queries) | |||
program excel worksheet to automatically not print blank rows | Excel Worksheet Functions | |||
automatically insert town name after entering zip code | Excel Discussion (Misc queries) | |||
Can you change something in Excel to automatically insert an equa. | Excel Discussion (Misc queries) | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions |