asking again, macro to insert rows

[Guest]

Example:
My sheet needs to insert a blank row under a number everytime it changes.
The column "C" may have 20 or so rows with the number 10 for example, the
next five might be the number 8, I need a row between the last number ten and
the first number eight. This may be repeated many times through the
spreadsheet.
As well, the adjacent columns "D" & "E" are the same as described above.
all 3 columns are continuous ie:
C D E
8
8
8
8
20
20
20
20
10
10
10
5
5
9
9
9
12
12
12
therefore I need to insert a row between 8 & 20, 20 & 10, 10 & 5 and etc.
I hope that I have articulated this well enough.

I've tried the following but it is for only one column and I assume it's the
reason I get a sytax error at:
cells(nr,1.select

sub blnkentery()
nr=Application.WorksheetFunction.counta("A:A")
for r = nr to 2 step -1
cells(nr,1.select
if cells(nr,1) <> cells(nr-1,1) then Selection.EntireRow.Insert
next r
end sub

please help
Thank You for your time
Luke

 

[PY & Associates]

cells(nr,1.select
close bracket missing

loop columns 2 and 3

 

[PY & Associates]

I suggest you get the biggest of the row number of the three columns,
loop all rows up from biggest row number
if cell cn<>cn-1 or dn<>dn-1or en<>en-1 then insert one row

 

[Guest]

Okay on the bracket but I don't understand "loop columns 2 and 3" could you
elaborate? I don't know VB that well.
Thank you
Luke

 

[Guest]

PY & Associates,
Thanks for that. I guess now I am trying to figure out how that would be
entered into VB as I don't know VB well enough :

sub blnkentery()
nr=Application.WorksheetFunction.counta("c:c")
for r = nr to 2 step -1
cells(nr,1.select)
if cells(nr,1) <> cells(nr-1,1) then Selection.EntireRow.Insert
next r
end sub

Thank you for your help thus far
Luke

 

[Bob Phillips]

Sub test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "C").End(xlUp).Row
For i = iLastRow - 1 To 2 Step -1
If Cells(i, "C").Value <> Cells(i - 1, "C").Value Then
Rows(i).Insert
End If
Next i

End Sub

 

[Bob Phillips]

Sorry, I posted the wrong version. Try this

Sub test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "C").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "C").Value <> "" Then
Cells(i, "F").Value = Cells(i, "C").Value
ElseIf Cells(i, "D").Value <> "" Then
Cells(i, "F").Value = Cells(i, "D").Value
Else
Cells(i, "F").Value = Cells(i, "E").Value
End If
Next i

For i = iLastRow - 1 To 2 Step -1
If Cells(i, "F").Value <> Cells(i - 1, "F").Value Then
Rows(i).Insert
End If
Next i

Range("F1").Resize(iLastRow).ClearContents

End Sub

 

[PY & Associates]

Bob's program is great.
I was think of this

Sub t()
rowc = Range("A65536").End(xlUp).Row
rowd = Range("B65536").End(xlUp).Row
rowe = Range("C65536").End(xlUp).Row
lrow = Application.WorksheetFunction.Max(rowc, rowd, rowe)
For i = lrow To 3 Step -1
If Cells(i, 1) <> Cells(i - 1, 1) Or Cells(i, 2) <> Cells(i - 1, 2) _
Or Cells(i, 3) <> Cells(i - 1, 3) Then Cells(i, 1).EntireRow.Insert
Next i
End Sub

 

[Bob Phillips]

Hi PY,

Yeah that works good too, when adjusted to the OP's ranges, if a bit more
obtuse <g>

Just a couple of comments.

First you need to step back to row 2, just in case it changes between row 1
and row 2.
second, I detest hard coding row count, and evangelise using Rows.Count

Sub t()
Dim lRow As Long
Dim i As Long
lRow = WorksheetFunction.Max(Range("C" & Rows.Count).End(xlUp).Row, _
Range("D" & Rows.Count).End(xlUp).Row, _
Range("E" & Rows.Count).End(xlUp).Row)
For i = lRow To 3 Step -1
If Cells(i, "C") <> Cells(i - 1, "C") Or _
Cells(i, "D") <> Cells(i - 1, "D") Or _
Cells(i, "E") <> Cells(i - 1, "E") Then _
Rows(i).Insert
Next i
End Sub

 

[PY & Associates]

Good suggestion.

1 What is OP's ranges please?
2 rows.count or 65536 is purely personal preference.
I choose 65536 because I can see this number;
I most likely cannot remember rows.count;
I am not as young as I want myself to be.
3 we are not sure what the REAL list looks like.
Is C a label, or column label, why cells(nrs,1) is under C?
so loop to 2 or 3 is only our demo how the code works.

We are learning from each other.
Great community.

 

[PY & Associates]

So I guessed OP from your response to Cell Color message

 

[Guest]

Thank you Bob and PY,
Bob the post you entered by mistake answered my fisrt question on single
columns and the second script was perfect for three columns.
Thank you very much... It works GREAT!
You guys are the best!
Luke

 

[Bob Phillips]

1 What is OP's ranges please?

OP - original poster, Luke in this case.

2 rows.count or 65536 is purely personal preference.
I choose 65536 because I can see this number;
I most likely cannot remember rows.count;
I am not as young as I want myself to be.

Well maybe. personally I cannot ever remember 65336 - oops there you go <g>

Also, 65536 will not work in Excel 95 for exam,ple, as there were not that
many rows, and will there ever be more?

3 we are not sure what the REAL list looks like.
Is C a label, or column label, why cells(nrs,1) is under C?
so loop to 2 or 3 is only our demo how the code works.

We are learning from each other.

No, you are absolutely right, but to be sure we both should have coded for
numbers.