asking again, macro to insert rows
 

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

cells(nr,1.select
close bracket missing

loop columns 2 and 3

"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

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


"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

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" wrote:

cells(nr,1.select
close bracket missing

loop columns 2 and 3

"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

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


"PY & Associates" wrote:

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


"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

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

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

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

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

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

So I guessed OP from your response to Cell Color message

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

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" wrote:

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

"PY & Associates" wrote in message
...

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.