Insert row macro

  • Thread starter Thread starter Beth
  • Start date Start date
B

Beth

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
 
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
 
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
 
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
 
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
 
That was Don's joke.

I think making the rows looked double spaced is better than adding extra rows,
too.
 
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



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

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message
news:[email protected]...
 
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 ...
 
Hello...

I'm kindda new in this area, but I have a task to add a line within a range of cell packed with formula.

And everytime I add a row and click the button "to run the monthly report", the other cells are showing ########.

Anyone has any idea how to add a row on such spreadsheet?
 
A row of #### could mean you column widths are too narrow but it might also
indicate a date problem. Try the following link to read how to fix cell
width. In particular, read about autofit.

http://www.uwec.edu/Help/Excel07/ws-rowcol.htm


The error as it relates to dates usually indicates problems with date
subtraction. Press F1, copy and paste the following into the Help screen to
find out more:

Correct a ##### error
 
Hi,

To really be of help you should show us the formula that is returning the
#######'s.
 
Back
Top