Info

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

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

1 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.lbProduct .RowSource = ""

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

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

.Addltem cell.Value Next cell End With

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

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

' Do the Advanced Filter to get unique list of customers 1 Figure 11.15 shows the worksheet state just before this line 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.lbRegion

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

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

.Addltem cell.Value Next cell End With

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

End Sub

Figure 11.15

The worksheet just before the macro runs the advanced filter.

0 0

Post a comment