"Insert Line Break-Macro"

V

vernerv

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
 
B

Bob Phillips

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)
 
T

Tom Ogilvy

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
 
V

vernerv

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
 
V

vernerv

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
 
B

Bob Phillips

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)
 
T

Tom Ogilvy

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

vernerv

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
 
T

Tom Ogilvy

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.
 
Top