NUMBER OF ROWS IN A DYNAMIC RANGE

[Larry]

HOWDY ALL, i REALLY APPRECIATE ALL THE HELP YOU PROVIDE.
I NEED TO LEARN HOW TO CONTROL THE NUMBER OF CELLS THAT WILL DISPLAY THE
DROPDOWN LIST IN A DYNAMIC RANGE. CURRENTLY:

I HAVE A NICE WORKBOOK SET UP BY THE YEAR OF AN AUTO CONTAINING TWO
WORKSHEETS, THAT REPRESENT EACH AUTO BY A SPECIAL NUMBER. EACH AUTOS SHEET
CONTAINS DESCREPENCIES FOUND WITH THE VEHICLE; EACH DESCREPENCY GETS IT'S OWN
SPECIAL NUMBER.
CURRENTLY I AM SET UP WITH A DYNAMIC RANGE DROP DOWN FOR DESCREPENCIES, AN
AUTO DATE INSERT, A NAMED RANGE DROP DOWN FOR INTIALS AND A SEQUENTIAL NUMBER
WORKSHEET IN ANOTHER WORKBOOK THAT IS HYPERLINKED TO THE COLUMN WHERE THE NEW
DESCREPENCY NUMBER GOES. HERE'S THE CODE:

IN MODULE 1 I HAVE:

Option Explicit

Sub Workbook_Open()
Columns("B:B").Select
Range("B3").Activate
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="WAIVER%20NO.xls", _
TextToDisplay:=""
End If
Else
End If
End Sub

NO SHEET 1OR2

IN SHEET 3 (LISTS)I HAVE:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

IN SHEET 4 (86x36236; an auto number)
I HAVE:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row > 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("NameList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
End Sub

I DID INSTALL A COMBO BOX AND SET THE ROWS BUT IT HAD NO EFFECT??

SO, AGAIN, i NEED TO LEARN HOW TO CONTROL THE NUMBER OF ROWS THAT WILL
DISPLAY THE DYNAMIC LIST; CURRENTLY IT WILL ONLY SHOW THE DROP DOWN UP TO ROW
17, AFTER THAT IT WILL NOT DISPLAY THE DROPDOWN. i CANNOT FIND WHERE THIS IS
DELINEATED, i SUSPECT MAYBE IT IS HELD IN MEMORY? SURE WOULD APPRECIATE SOME
HELP. i YOU CAN'T REPLY BY 4:30 CST, MY EMAIL IS (e-mail address removed).
HAVE A GOOD WEEKEND FOLKS.