creating a line break

[********Meg]

I have about 2000 lines /rows in excel.
one of the columns has a code (a class code in our case)
Is there any way that I can have the software insert a line break when that
class code changes. I would like to isolate the information for each class

Thanks

Excel 2003
Windows XP

 

[Gord Dibben]

Assuming column A is the class code column. Edit to suit any other column.

Sub InsertBreak_At_Change()
Dim I As Long
Dim rng As Range
Set rng = ActiveSheet.Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))
For I = rng.Rows.Count To 1 Step -1
If rng(I).Row = 1 Then Exit Sub
If rng(I) <> rng(I - 1) And Not IsEmpty _
(rng(I - 1)) Then
With rng(I)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Roger Govier]

Hi

Gord interpreted your request as wanting a Page break between classes.
I read it as wanting a row inserted between the classes.
If that is the case, then just change one section of Gord's code from
With rng(I)
.PageBreak = xlPageBreakManual
End With

to

With rng(I)
.EntireRow.Insert
End With

 

[Gord Dibben]

Good point Roger.

Thanks, Gord

 

[********Meg]

Actually a page break, would probably be the best, but I am uncertain as to
where I put the code

Thanks

 

[********Meg]

and if it another column, where am I editing? where is says Cell (1,)?

 

[Gord Dibben]

Would be edited thusly for other row or column.

Set rng = ActiveSheet.Range(Cells(row, column), _
Cells(Rows.Count, column).End(xlUp))

So for Column C..................

Set rng = ActiveSheet.Range(Cells(1, 3), _
Cells(Rows.Count, 3).End(xlUp))

rng will be from C1 to bottom of used range in column C

Say you had a title row and did not want it included.............

Set rng = ActiveSheet.Range(Cells(2, 3), _
Cells(Rows.Count, 3).End(xlUp))

rng will be from C2 to bottom of used range in column C

Demo macro........................

Sub test_range()
Dim rng As Range
Set rng = ActiveSheet.Range(Cells(2, 3), _
Cells(Rows.Count, 3).End(xlUp))
MsgBox rng.Address
End Sub

Play with the row and column numbers and see what rng.address returns.


Gord

 

[Gord Dibben]

I think an inserted row would be better but you decide..........you could do
both<g>

See reply to your other post for editing the macro for columns and rows.

Where do you put the code..........?

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord

 

[Gord Dibben]

One more editing trick if you can't remember what the column numbers are.

Set rng = ActiveSheet.Range(Cells(2, "C"), _
Cells(Rows.Count, "C").End(xlUp))


Gord