Hello,

OS:WINXP/Office2000

I have an excel sheet with 5419 rows and the column E has numbers that
increases after certain rows. Eg: All rows from row 1 to row 9, is "1"
then all rows from row 10 to row 34 has "2" and so on.. till "379"

Now:
1
1
1
1
1
1
1
2
2
2
2
2
2
3
3
3

I need a macro to make it look like:
1
1
1
1
1
1
1

2
2
2
2
2
2
2

3
3
3
3
3
3

I need to write a macro to insert a line break after the row that ends
with 1 and similarily a line break after the row that ends with 2 and
so on.. till the nth(379) numbered row

Can anyone please help? Thanks
Verner


---
Message posted from http://www.ExcelForum.com/

Hi Verner,

Here's some simple code to do it

Sub InsertBlanks()
Dim i As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "A").Value < Cells(i - 1, "A").Value Then
Cells(i, "A").EntireRow.Insert
i = i - 1 ' skip new line
End If
Next i

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"vernerv " wrote in message
...
Hello,

OS:WINXP/Office2000

I have an excel sheet with 5419 rows and the column E has numbers that
increases after certain rows. Eg: All rows from row 1 to row 9, is "1"
then all rows from row 10 to row 34 has "2" and so on.. till "379"

Now:
1
1
1
1
1
1
1
2
2
2
2
2
2
3
3
3

I need a macro to make it look like:
1
1
1
1
1
1
1

2
2
2
2
2
2
2

3
3
3
3
3
3

I need to write a macro to insert a line break after the row that ends
with 1 and similarily a line break after the row that ends with 2 and
so on.. till the nth(379) numbered row

Can anyone please help? Thanks
Verner


---
Message posted from http://www.ExcelForum.com/

assume by line break you mean insert a blank row.

Dim rng as Range, i as Long
set rng = cells(rows.count,5).End(xlup)
for i = rng.row to 1 step - 1
if cells(i+1,5) < cells(i,5) then
cells(i+1,5).EntireRow.Insert
end if
Next

--
Regards,
Tom Ogilvy


"vernerv " wrote in message
...
Hello,

OS:WINXP/Office2000

I have an excel sheet with 5419 rows and the column E has numbers that
increases after certain rows. Eg: All rows from row 1 to row 9, is "1"
then all rows from row 10 to row 34 has "2" and so on.. till "379"

Now:
1
1
1
1
1
1
1
2
2
2
2
2
2
3
3
3

I need a macro to make it look like:
1
1
1
1
1
1
1

2
2
2
2
2
2
2

3
3
3
3
3
3

I need to write a macro to insert a line break after the row that ends
with 1 and similarily a line break after the row that ends with 2 and
so on.. till the nth(379) numbered row

Can anyone please help? Thanks
Verner


---
Message posted from http://www.ExcelForum.com/

If you search the forum you will find several example

--
Message posted from http://www.ExcelForum.com

... about 20 messages below this on

--
Message posted from http://www.ExcelForum.com

Hey Bob,

Thanks for the code, but there's a problem.

The output looks like this:

166930.000000 5272038.000000 1 1.500000
165949.000000 5272484.000000 1 1.500000

165325.000000 5273019.000000 1 1.500000
164879.000000 5273733.000000 1 1.500000

164826.000000 5273945.000000 1 1.500000
163541.000000 5273019.000000 2 1.500000

163720.000000 5274804.000000 2 1.500000
163987.000000 5276409.000000 2 1.500000

But I would like it like this:

166930.000000 5272038.000000 1 1.500000
165949.000000 5272484.000000 1 1.500000
165325.000000 5273019.000000 1 1.500000
164879.000000 5273733.000000 1 1.500000
164826.000000 5273945.000000 1 1.500000

163541.000000 5273019.000000 2 1.500000
163720.000000 5274804.000000 2 1.500000
163987.000000 5276409.000000 2 1.500000


The number after 1,2.....till 379. I would need to have a line breaks
after all the 1's, the 2's and like that till the last one, ie. 379

Any suggestion?

Cheers
Elvis


---
Message posted from http://www.ExcelForum.com/

Hey Tom,

Many thanks for the response, but the macro just would'nt run.

This is what I have:

166930.000000 5272038.000000 1 1.500000
165949.000000 5272484.000000 1 1.500000
165325.000000 5273019.000000 1 1.500000
164879.000000 5273733.000000 1 1.500000
164826.000000 5273945.000000 1 1.500000
163541.000000 5273019.000000 2 1.500000
163720.000000 5274804.000000 2 1.500000
163987.000000 5276409.000000 2 1.500000

This is what I would need:

166930.000000 5272038.000000 1 1.500000
165949.000000 5272484.000000 1 1.500000
165325.000000 5273019.000000 1 1.500000
164879.000000 5273733.000000 1 1.500000
164826.000000 5273945.000000 1 1.500000

163541.000000 5273019.000000 2 1.500000
163720.000000 5274804.000000 2 1.500000
163987.000000 5276409.000000 2 1.500000


The number after 1,2.....till 379. I would need to have a line break
after all the 1's, the 2's and like that till the last one, ie. 379

Any suggestion?

Cheers
Elvi

--
Message posted from http://www.ExcelForum.com

Venerv,

Just change all references to "A" to "C"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"vernerv " wrote in message
...
Hey Bob,

Thanks for the code, but there's a problem.

The output looks like this:

166930.000000 5272038.000000 1 1.500000
165949.000000 5272484.000000 1 1.500000

165325.000000 5273019.000000 1 1.500000
164879.000000 5273733.000000 1 1.500000

164826.000000 5273945.000000 1 1.500000
163541.000000 5273019.000000 2 1.500000

163720.000000 5274804.000000 2 1.500000
163987.000000 5276409.000000 2 1.500000

But I would like it like this:

166930.000000 5272038.000000 1 1.500000
165949.000000 5272484.000000 1 1.500000
165325.000000 5273019.000000 1 1.500000
164879.000000 5273733.000000 1 1.500000
164826.000000 5273945.000000 1 1.500000

163541.000000 5273019.000000 2 1.500000
163720.000000 5274804.000000 2 1.500000
163987.000000 5276409.000000 2 1.500000


The number after 1,2.....till 379. I would need to have a line breaks
after all the 1's, the 2's and like that till the last one, ie. 379

Any suggestion?

Cheers
Elvis


---
Message posted from http://www.ExcelForum.com/

If I paste your numbers into cell C1 (so they fill columns C to F), then it
runs fine. You said your changing numbers were in Column E.

I have an excel sheet with 5419 rows and the column E has numbers that
increases after certain rows.

If your numbers are in columns A to D, then this modification works

Sub Tester1()
Dim rng As Range, i As Long
Set rng = Cells(Rows.Count, 3).End(xlUp)
For i = rng.Row To 1 Step -1
If Cells(i + 1, 3) < Cells(i, 3) Then
Cells(i + 1, 3).EntireRow.Insert
End If
Next

End Sub

If you give incorrect information, don't expect the solution offered to
work.

--
Regards,
Tom Ogilvy

"vernerv " wrote in message
...
Hey Tom,

Many thanks for the response, but the macro just would'nt run.

This is what I have:

166930.000000 5272038.000000 1 1.500000
165949.000000 5272484.000000 1 1.500000
165325.000000 5273019.000000 1 1.500000
164879.000000 5273733.000000 1 1.500000
164826.000000 5273945.000000 1 1.500000
163541.000000 5273019.000000 2 1.500000
163720.000000 5274804.000000 2 1.500000
163987.000000 5276409.000000 2 1.500000

This is what I would need:

166930.000000 5272038.000000 1 1.500000
165949.000000 5272484.000000 1 1.500000
165325.000000 5273019.000000 1 1.500000
164879.000000 5273733.000000 1 1.500000
164826.000000 5273945.000000 1 1.500000

163541.000000 5273019.000000 2 1.500000
163720.000000 5274804.000000 2 1.500000
163987.000000 5276409.000000 2 1.500000


The number after 1,2.....till 379. I would need to have a line breaks
after all the 1's, the 2's and like that till the last one, ie. 379

Any suggestion?

Cheers
Elvis


---
Message posted from http://www.ExcelForum.com/

Hey Tom,

Thanks for the macro

I know I'm pushing my luck, but is there anyway I can enter
value(-111) inside the blank cells of the first 4 columns once the
have been created using this macro?

Macro:

Sub InsertBlanks()
Dim i As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "A").Value < Cells(i - 1, "A").Value Then
Cells(i, "A").EntireRow.Insert
i = i - 1 ' skip new line
End If
Next i
End Sub

I have changed the column"A" to "C" as I need that to check for th
incremental number within that column and introduce the blank row. No
in the 4 columns I would require the value -111 to be introduced usin
the same macro.

This is my actual requirment:
25.345085 51.367877 1 0
25.784345 50.356682 1 0
-111 -111 -111 -111
66.179288 47.117222 2 0
66.164537 47.128142 2 0
-111 -111 -111 -111


Any suggestions?

Thanks a lot!
Verne

--
Message posted from http://www.ExcelForum.com

Sub Tester1()
Dim rng As Range, i As Long
Set rng = Cells(Rows.Count, 3).End(xlUp)
For i = rng.Row To 1 Step -1
If Cells(i + 1, 3) < Cells(i, 3) Then
Cells(i + 1, 3).EntireRow.Insert
Cells(i + 1, 1).Resize(1, 4).Value = -111
End If
Next
End Sub


Worked fine for me.

--
Regards,
Tom Ogilvy


"vernerv " wrote in message
...
Hey Tom,

Thanks for the macro

I know I'm pushing my luck, but is there anyway I can enter a
value(-111) inside the blank cells of the first 4 columns once they
have been created using this macro?

Macro:

Sub InsertBlanks()
Dim i As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "A").Value < Cells(i - 1, "A").Value Then
Cells(i, "A").EntireRow.Insert
i = i - 1 ' skip new line
End If
Next i
End Sub

I have changed the column"A" to "C" as I need that to check for the
incremental number within that column and introduce the blank row. Now
in the 4 columns I would require the value -111 to be introduced using
the same macro.

This is my actual requirment:
25.345085 51.367877 1 0
25.784345 50.356682 1 0
-111 -111 -111 -111
66.179288 47.117222 2 0
66.164537 47.128142 2 0
-111 -111 -111 -111


Any suggestions?

Thanks a lot!
Verner


---
Message posted from http://www.ExcelForum.com/