asking again, macro to insert rows

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

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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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.
 
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
 
1 What is OP's ranges please?
Click to expand...

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

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

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