Greetings,

I have 2 sheets: "Input" and "Budget".

The source list is a named range called "NameSource" (D3:D839) which
is the list that the new names are being added.

Special problem: the names are not in any particular order and they
are added into the various days of the month. they are not in order,
there are many duplicates and there are a lot of blanks in the middle
of this range called "NameSource"

I need to access this list of customer names, but I need the list
alphabetized and blanks removed.

I tried to do this by coping the range("NameSource") into a helper
sheet called "Customer", into a named range called "NameDest". Then
in a sheet level sub (Worksheet_Change event sub) in the sheet called
"Input", I called up a public sub from a general module.

Here is the sheet sub from sheet "Input":

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
GetSourceNameList
On Error GoTo 0
End Sub

Here is the general module sub:

Public Sub GetSourceNameList()
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlManual
End With
Range("NameSource").Select
Selection.Copy
Sheets("Customers").Select
Range("A3").Select
Selection.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
Range("A1:A839").AdvancedFilter _
Action:=xlFilterInPlace, _
Unique:=True
ActiveCell.SpecialCells(xlLastCell).Select
Range("NameDest").Select
Selection.Sort _
Key1:=Range("A3"), _
Order1:=xlAscending, _
Header:=xlno, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Input").Select
Range("C3").Select
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlAutomatic
End With

End Sub

This is what the macro recorded gave. It almost works!!!!

There are a couple of "challenges" left yet. :^}

1st problem: the named range has no blank rows showing. There are a
few hundred blank rows, but the blank rows all have a height = 0 (the
default height is 20) and as such I only SEE the rows with data in
them. This looks ok on the surface, but in the "Budget" sheet where
the cells in column A are linked to the "Customer" sheet, each
customer name needs to be in alphabetical order with no blanks. The
way that this macro is working, this list of customer names has the
name in each row and the remainder of the 27 rows in each day are
blank. The 2nd day also has 27 rows reserved for it. First are the
entries for that day and the rest is left blank. And so on until after
the 31st day, then it stops.

Also, this code runs real slow!!!

I did make a macro to make all of the row heights, in the list, equal
to 20. This works, but it take a couple of HOURS to resize these 839
rows!!!

Anyone have any ideas as to where to look to fix this problem?

Any help is greatly appreciated.

-Minitman