Insert row macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Beth - 22 Dec 2006 06:42 GMT

I'm trying to build a macro that runs down my culumn C and inserts 1 blank row each time there is a change in the value from the previous cell.

For example, if I have 100 rows of data and the first five are "CX104" and then the next ten are "CX105", and then the five after that are "CX106" and so on - that it would insert a blank row between the CX104 and the CX105 info, and a blank row between the CX105 and CX106 info...so in other words, if C2 does not equal C1, insert row...

Any ideas?

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Reply to this Message

Gary''s Student - 22 Dec 2006 10:23 GMT

Hi Beth:

This should help:

Sub marine()
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, "C").Value = Cells(i - 1, "C").Value Then
Else
Cells(i, "C").EntireRow.Insert
End If
Next
End Sub

Signature

Gary's Student

> I'm trying to build a macro that runs down my culumn C and inserts 1 blank row each time there is a change in the value from the previous cell.
>
[quoted text clipped - 4 lines]
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com

Reply to this Message

Don Guillett - 22 Dec 2006 13:02 GMT

or a bit shorter
Sub marine()
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, "C").Value <> Cells(i - 1, "C").Value Then rows(i).Insert
Next i
End Sub

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hi Beth:
>
[quoted text clipped - 24 lines]
>> EggHeadCafe.com - .NET Developer Portal of Choice
>> http://www.eggheadcafe.com

Reply to this Message

Dave Peterson - 22 Dec 2006 14:43 GMT

Excel has a feature data|subtotals that does close to what you want. It also
provides the ability to sum/count (some kind of summary) for fields in that
group.

If you've tried that and don't like it, ...

Just a suggestion, I wouldn't insert an empty row between the groups. It can
make other things more difficult to do
(charting/sorting/filtering/pivottabling(?)).

I'd just increase the rowheight so that the next group looks double spaced.

Option Explicit
Sub marine()
Dim LastRow As Long
Dim iRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For iRow = LastRow To 2 Step -1
If .Cells(iRow, "C").Value <> .Cells(iRow - 1, "C").Value Then
.Rows(iRow).RowHeight = .Rows(iRow).RowHeight * 2
End If
Next iRow
End With
End Sub

Signature

Dave Peterson

Reply to this Message

KC Rippstein - 22 Dec 2006 17:05 GMT

You guys crack me up...sub marine
Dave, I run the insert row macro every month and never thought to do a
double space instead. Brilliant idea!
- KC

> Excel has a feature data|subtotals that does close to what you want. It
> also
[quoted text clipped - 41 lines]
>> EggHeadCafe.com - .NET Developer Portal of Choice
>> http://www.eggheadcafe.com

Reply to this Message

Dave Peterson - 22 Dec 2006 17:48 GMT

That was Don's joke.

I think making the rows looked double spaced is better than adding extra rows,
too.

> You guys crack me up...sub marine
> Dave, I run the insert row macro every month and never thought to do a
[quoted text clipped - 50 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

David McRitchie - 23 Dec 2006 04:27 GMT

I don't think the solutions that inserted rows allowed
for rerunning, i.e. not inserting a row between an empty row
and a row with content.
Insert Blank Rows on Column A change of value (#ColAchg)
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#ColAchg
you would modify for Col C instead of A by changing
"A" to "C" and
Cells(i,1) to Cells(i, 3)
assuming that it is only column C that matters, if you
have changing data in A & B that also has to be changed
then you will have to check everything.

Anyway what about instead of inserting rows
you used color from Conditional Formatting to
color alternate Groups.

In order to that you would need a helper column
that need not be visible and need not be printed
Color Grouping with alternating colors (#grouping)
http://www.mvps.org/dmcritchie/excel/condfmt.htm#grouping

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> I'm trying to build a macro that runs down my column C and inserts 1 blank row each time there is a change in the value from the

previous cell.

> For example, if I have 100 rows of data and the first five are "CX104" and then the next ten are "CX105", and then the five after that are "CX106" and so on - that it would insert a blank row between the CX104 and the CX105 info, and a blank row between the

CX105 and CX106 info...so in other words, if C2 does not equal C1, insert row...

> Any ideas?
>
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com

Reply to this Message

Loriza Andrade - 14 Oct 2007 18:30 GMT

Hello to everybody,

Any help on this one would be great.

I've got a problem that's causing me a headache. I have a document with 5 columns and lots and lots of rows.

I need to do a search cell by cell (in the first column) for any cells that contain the text "changeType". When a cell is found, I need excel to insert two entire new rows below the current cell and insert the text "modify" into the two new rows (in the first column). I also need to insert, in the first new row, but on column 5, the text "IN" and on the second new row, still on column 5, the text "OUT". The problem is that these two rows need to be inserted after the second time the text "changeType" appears, like the example below:

eg.

x x x x x
x x x x x
x x x x x
changeType x x x x
changeType x x x x
x x x x x
x x x x x

Becomes:

x x x x x
x x x x x
x x x x x
changeType x x x x
changeType x x x x
modify IN
modify OUT
x x x x x
x x x x x

I dont use excel much so i'm pretty novice.

Thanks for any help you can offer

Loriza

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Reply to this Message

Sandy Mann - 14 Oct 2007 20:13 GMT

Try something like:

Option Explicit
Sub Change_Type()
Dim x As Long
Dim ChangeCount As Integer

x = 1

Do Until Cells(x, 1).Value = ""
If Cells(x, 1).Value = "Change Type" Then
If ChangeCount = 1 Then
Cells(x, 1).Offset(1, 0).Resize(2, 1).EntireRow.Insert
Range(Cells(x, 1).Offset(1, 0), _
Cells(x, 1).Offset(2, 0)).Value = "Modify"

Cells(x, 5).Offset(1, 0).Value = "IN"
Cells(x, 5).Offset(2, 0).Value = "OUT"
ChangeCount = -1
End If
ChangeCount = ChangeCount + 1
End If

x = x + 1
Loop
End Sub

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Hello to everybody,
>
[quoted text clipped - 42 lines]
> EggHeadCafe - .NET Developer Portal of Choice
> http://www.eggheadcafe.com

Reply to this Message

David McRitchie - 15 Oct 2007 02:48 GMT

When inserting or deleting rows the code is a lot more
straight forward if the checking starts at the bottom of used range
and goes up. If you start at the top and go down then
you have to make additional adjustments after inserting/deleting.
Wonder if it is possible to have three together in column
instead of two.

Also be aware that in VBA comparison of text is case sensitive.
See http://www.mvps.org/dmcritchie/excel/strings.htm#sensitivity
'as textual comparison (1: vbTextCompare)

I didn't try either but for the text comparisons:
in Sandy's change/to
If Cells(x, 1).Value = "Change Type" Then
If InStr(1, Cells(x,1).value, "Change Type", 1) Then

or in Don's change/to "ct" is actually "changeType" per posting
If Cells(i, mc) = "ct" And Cells(i - 1, mc) = "ct" Then
If InStr(1, Cells(i,mc) = "ct" And InStr(1, Cells(i-1, mc) = "ct" Then

Another way to test is to change to caps or lowercase but not as fast
If UCASE(Cells(i,mc)) = "CT" And ...

Signature

HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

> Try something like:
>
[quoted text clipped - 69 lines]
>> EggHeadCafe - .NET Developer Portal of Choice
>> http://www.eggheadcafe.com

Reply to this Message

Don Guillett - 14 Oct 2007 20:24 GMT

Try this idea

Sub insertrowsif()
mc = 1 '"a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 15 Step -1
If Cells(i, mc) = "ct" And Cells(i - 1, mc) = "ct" Then
Rows(i + 1).Resize(2).Insert
Cells(i + 1, mc).Resize(2).Value = "modify"
Cells(i + 1, mc).Offset(, 4) = "In"
Cells(i + 2, mc).Offset(, 4) = "Out"
End If
Next
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hello to everybody,
>
[quoted text clipped - 42 lines]
> EggHeadCafe - .NET Developer Portal of Choice
> http://www.eggheadcafe.com

Reply to this Message

Rate this thread: