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.
"Bob Phillips" wrote in message
...
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
--
HTH
Bob Phillips
"PY & Associates" wrote in message
...
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" wrote in message
...
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
--
HTH
Bob Phillips
"Bob Phillips" wrote in message
...
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
--
HTH
Bob Phillips
"Luke" wrote in message
...
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
|