Page breaks at letter of alphabet

[husky84]

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.

 

[Damon Ostrander]

Hi husky84,

Here is an example macro that does what you describe.

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]

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]

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]

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