End If Next i Unload Me End Sub

Private Sub UserForm_Initialize() Dim IRange As Range Dim ORange As Range

1 Find the size of today's dataset FinalRow = Cells(65536, 1).End(xlUp).Row NextCol = Cells(1, 255).End(xlToLeft).Column + 2

' Set up output range. Copy heading from D1 there Range("D1").Copy Destination:=Cells(1, NextCol) Set ORange = Cells(1, NextCol)

' Define the Input Range

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2)

' Do the Advanced Filter to get unique list of customers IRange.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=ORange, Unique:=True

' Determine how many unique customers we have LastRow = Cells(65536, NextCol).End(xlUp).Row

' Sort the data

Cells(1, NextCol).Resize(LastRow, 1).Sort Key1:=Cells(1, NextCol), Order1:=xlAscending, Header:=xlYes

With Me.lbCust

FinalRow = Range("J65536").End(xlUp).Row

For Each cell In Cells(2, NextCol).Resize(LastRow - 1, 1)

.AddItem cell.Value Next cell End With

' Erase the temporary list of customers Cells(1, NextCol).Resize(LastRow, 1).Clear End Sub

Launch this form with a simple module such as this:

Sub ShowCustForm() frmReport.Show End Sub

Your clients are presented with a list of all valid customers from the dataset. Because the list box's MultiSelect property is set to allow it, they can select any number of customers, as shown in Figure 11.6.

Figure 11.6

Your clients will have a list of customers from which to select quickly. Using an advanced filter on even a 60,000-row dataset is far faster than setting up a class to populate the list box.

Create Report for Customer

Select Customer(s) to Report:

Create Report for Customer

Select Customer(s) to Report:

0 0

Post a comment