Multi column sorting for print out

QuestionEdit

I created a separate Report worksheet for printing. This sheet has various sort buttons (Surname, Location, Date of Birth, etc.) and is working fine with one exception.

When I do a multi-alphabetic sort (surname + first name + middle name) all the blank records come to the top of the report. For example, if there were 1,000 graves in the cemetery but only 500 of them are used, the "A" surnames start at row 500 on the Report worksheet so the first 10 pages of printouts would be blank.

Here is my code from the sort routine:

- - - - - - - - - - - - - - - -

Sub Sort_Surname_Given_Middle() ' ' Sort_Surname_Given_Middle Macro ' Macro recorded 7/14/2009 by Robert Chudek '

'

   Range("B12:AN12,AP12:AS12,AU12").Select
   Range("AP12").Activate
   Selection.Interior.ColorIndex = 35
   Range("K12:M12").Select
   Selection.Interior.ColorIndex = 38
   Rows("13:3040").Select
   Selection.Sort Key1:=Range("K13"), Order1:=xlAscending, Key2:=Range("L13" _
       ), Order2:=xlAscending, Key3:=Range("M13"), Order3:=xlAscending, Header _
       :=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
       , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
       xlSortNormal
   Range("K12:M12").Select
   Selection.Interior.ColorIndex = 36
   Range("K12").Select
   Selection.Interior.ColorIndex = 6
   Range("K8:M8").Select

End Sub - - - - - - - - - - - - - - - - - -

I haven't found a reference for the syntax/options of the OrderCustom=1 or DataOption1= commands. I speculate one of them might control the behavior I am seeing.

Can you point me to a reference or provide insight?

Thank you!

AnswerEdit

Robert,

I don't think you have a fix with either of those.

The OrderCustom1 is an index into the custom list. The custom list has stuff like

Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

the names of the months and so forth.

If you use Custom List, you would need to put every string in the list that you are going to sort. You can't set up a a sequence of characters that are used for sorting.

The DataOption1, 2 and 3 each align with the 3 available Keys.

The argument can be the constants:

xlSortNormal 0 default. Sorts numeric and text data separately. xlSortTextAsNumbers 1 Treat text as numeric data for the sort.

Generally the solution for your problem is to put in something like ZZZZZZZ in the blank cells that your are going to sort. Do the sort, then remove the ZZZZZZ

Sub Sort_Surname_Given_Middle() ' ' Sort_Surname_Given_Middle Macro ' Macro recorded 7/14/2009 by Robert Chudek '

'

  Dim r as Range, r1 as Range, rw as Long
  Range("B12:AN12,AP12:AS12,AU12").Select
  Range("AP12").Activate
  Selection.Interior.ColorIndex = 35
  Range("K12:M12").Select
  Selection.Interior.ColorIndex = 38
  ' find the last row in the sheet
  rw = activesheet.Range("a1").specialcells(xlCellTypeLastCell).row
  set r = Range("K13",cells(rw,13))
  ' restrict it to the cells that are blank
  set r1 = r.specialcells(xlBlanks)
  ' put ZZZZZZZZZ in those cells
  r1.Value = "ZZZZZZZZZ"
  ' sort just up to the used range so you don't pull in extra blank rows
  Rows("13:" & rw).Select
  Selection.Sort Key1:=Range("K13"), Order1:=xlAscending, Key2:=Range("L13" _
      ), Order2:=xlAscending, Key3:=Range("M13"), Order3:=xlAscending, Header _
      :=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
      , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
      xlSortNormal
  ' get rid of the ZZZZZZZZZ
  r.Replace What:="ZZZZZZZZZ", Replacement:="", LookAt:=xlWhole, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False 
  Range("K12:M12").Select
  Selection.Interior.ColorIndex = 36
  Range("K12").Select
  Selection.Interior.ColorIndex = 6
  Range("K8:M8").Select

End Sub