Lists work their way into just about everything we do. Ordinarily, you might not think of worksheet data as a list, but that data can quickly turn into one, depending on your needs. That need becomes a bit more complicated if you want to reduce the data to a unique list. Fortunately, Excel�s filter feature can create a quick list of existing data and return only unique items. You can create the list manually or automate the task using VBA.
Note: This article is also available as a PDF download.
Manually, the easy way
With just a few clicks, Excel�s Data feature can create a unique list from a list of values. The short list of order information in Figure A is from the Access sample file Northwind.mdb specifically, it�s from the Order Details table. Notice that column H contains a unique list of order numbers from column A.
Figure A
To create the unique list, follow this simple three-step process:
- Select the first cell in the source list. In this case, that�s cell A1.
- Choose Filter from the Data menu. Then, select Advanced Filter from the submenu. Excel 2007 users will find the Filter options on the Data tab in the Sort And Filter group.
- In the Advanced Filter dialog box, click the Copy To Another Location option. Fill in the Copy To range. A single cell, such as H1 is adequate; Excel interprets a single cell as the top cell in the resulting list. Check the Unique Records Only option, as shown in Figure B, and then click OK.
Figure B
If you need a quick list and it doesn�t matter where the list is, use the built-in feature. However, if you want to use the list in some way, the quick way might not be adequate. Suppose you want to display the unique list in a combo box, as shown inFigure C. To do so, you could set the combo box control�s Row Source property to the appropriate range. In this case, that�s H2:H7, as shown in Figure D.
Figure C
Figure D
If you know that the list will never change, this solution works. Execute it one time and move on. However, this approach isn�t practical if the source data changes.
VBA, the automated way
A source list that changes creates a special problem. Creating a new list of unique items isn�t hard, but you don�t want to update the combo control�s Row Source property every time the original list changes. In this case, it�s best to automate the entire process. To do so, we�ll use VBA�s AdvancedFilter method.
First, create two ranges: the list�s source data and a target range for the unique list. Select the source data; in this case, that�s cells A2:A18. From the Insert menu, choose Name and then select Define. Excel will automatically assume the content of A1, the string OrderID, as the name. Click OK. Next, select cell H1 and name it UniqueList using the same process.
Next, you need a user form and a combo box. Press Alt + F11 to launch the Visual Basic Editor (VBE). From the Insert menu, choose UserForm. If necessary, click the Toolbox button to launch the Toolbox and drag a combo box control to the user form. Name the combo box control cboUniqueList. Save the user form as UserForm1.
Now, you�re ready to enter the code that automates the list. Choose Module from the Insert menu. Then, enter the function in Listing A. This function creates a unique list from the data in the OrderID named range, populates cboUniqueList with that list, displays UserForm1, and then deletes the unique list from the sheet.
Listing A
Function UniqueList()
'Populate control with
'unique list.
Range("OrderID" ).AdvancedFilter Action:=xlFilterCop y, _ CopyToRange: =Range("UniqueLi st"), Unique:=True 'Set combo control's Row Source property.
Range("UniqueList" ).Activate UserForm1.cboUnique List.RowSource = Selection.CurrentRe gion.Address
'Display user form.
UserForm1.Show
Selection.CurrentRe gion.Clear
End Function
The AdvancedFilter method automates the feature discussed in the previous section. This method uses the following syntax:
range.AdvancedFilte r(action, criteriarange, copytorange, unique)
where range is a range object. Table A lists the method�s other arguments. After creating the list, the code sets the combo box control�s Row Source property to the unique list in column H and then opens the user form with a populated combo control (Figure C).
Table A
| Argument | Explanation |
| action | Required constant: xlFilterCopy or xlFilterInPlace. Both are self-explanatory. If you create the list in place, Excel doesn�t delete items, it simply hides them. |
| criteriarange | Optional variant that defines criteria used to filter the list. |
| copytorange | Optional variant that specifies where VBA copies the list. |
| unique | Optional variant that determines if the list contains only unique values. The default value is False. |
A few noteworthy points
Because the code relies on the CurrentRegion property, be sure to locate the unique list in an out-of-the-way place where there�s no chance that the resulting list will run into existing data. Avoid giving the unique list�s range name more than one cell unless you know the exact size of the resulting list. The AdvancedFilter method�s copytorangeneeds only one cell. If the source list grows, you�ll need to update the named range�s dimensions to include new items before running the function. A shrinking list doesn�t present a problem. The code doesn�t sort the list, but you could add that capability to the code. The sample code contains no error handling, so be sure to test it thoroughly within your application and accommodate potential errors
No comments:
Post a Comment