"Insert Line Break-Macro"

  • Thread starter Thread starter vernerv
  • Start date Start date
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
 
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)
 
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
 
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
 
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
 
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)
 
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.
 
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
 
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.
 
Back
Top