how do I insert a blank row when a value changes, automatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...

HTH
Kostis Vezerides
 
Perfect - thank you

vezerid said:
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

HTH
Kostis Vezerides
Click to expand...
 
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
 
Back
Top