Page breaks at letter of alphabet

husky84

New Member
Joined
Oct 5, 2006
Messages
3
I have a sheet with 10,000 records (peoples' names) that I need to print and give to businesses. I would like to insert page breaks at the beginning of each letter of the alphabet so I can easily index the printed book. This list will be updated frequently with new names. I have searched all of the posts regarding "page breaks" but none are involving the letters of the alphabet. Thanks in advance.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi husky84,

Here is an example macro that does what you describe.

Code:
Sub AlphaPageBreaks()

   'inserts page breaks between each letter of the alphabet
   'Assumes column A
   'removes old page breaks
   'Assumes no column headers
   
   Dim iRow    As Long
   
   'remove all page breaks
   Rows.PageBreak = xlPageBreakNone
   
   For iRow = 2 To Sheet1.Range("A65536").End(xlUp).Row
      If UCase(Left(Cells(iRow, "A"), 1)) <> UCase(Left(Cells(iRow - 1, "A"), 1)) Then
         Rows(iRow).PageBreak = xlPageBreakManual
         Application.StatusBar = "Section " & UCase(Left(Cells(iRow - 1, "A"), 1))
      End If
   Next iRow
   
   Application.StatusBar = False
   
End Sub

As you can see from the comments, it assumes the column containing the primary sort key is A, but I think you can see how this can be easily changed.

To install this macro, go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM) and paste this code into the Code pane. To run it go back to Excel and use Alt-TMM (and click Options if you would like to assign a keyboard shortcut).

Keep Excelling.

Damon
 

husky84

New Member
Joined
Oct 5, 2006
Messages
3
Hello Damon,

Thank you very, very much! That worked perfectly and your post was very clear. I am sure by surfing this site I would be able to get lots of input, but I would like to ask you if you have any suggested reference books pertaining to writing scripts in Excel?

Thanks again, Damon. You are my hero!

Tony
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Tony,

In the past I have always recommended the books on VBA programming in Excel by John Walkenbach. But it has been quite a while since I last evaluated the books available on this topic--but I'm sure there are many new ones. I recommend you visit a major bookstore and look at what they have on the shelves. The last time I looked there were several, and comparing them there without having to buy first saved me a lot of $.

Once you get started and learn the basics you will find the built-in help files to be very useful. To get the VBA helps go to the VBE (Visual Basic Editor - keyboard Alt-TMVH) in Excel or any Office application.

An important note: the VBA scripting language is the same in all Office applications. What is different is the object models of these applications, and if you want to use VBA to do things in Excel you must become familiar with the Excel object model. Learning VBA is usually 80% object models and 20% VBA language. You can learn the object model from the VBA helps as well. A good starting point for Excel is to look up the topic Microsoft Excel Objects, which gives you an expandable diagram showing the Excel objects, and their hierarchy.

Damon
 

husky84

New Member
Joined
Oct 5, 2006
Messages
3
Damon,

Thank you for your advice. I have taken two classes on VBA and own a couple of books but since I am not into programming and can never think of anything I would like to build it has been hard to start. I do use Excel quite a bit so this may be the way for me to get into VBA. I have no other programming experience. I will go to Borders or Barnes & Noble and poke around. I appreciate your help. Thanks for the info on the Walkenbach book as well.

Sincerely,

Tony
 

Forum statistics

Threads
1,141,588
Messages
5,707,264
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top