Microsoft Office Tutorials and References
In Depth Information
AUTO-NUMBER RECORDS AND COLUMNS IN AN EXCEL DATABASE
Next
UniqArray = UniqueDic.Items
Range("H1").Resize(UniqueDic.Count, 1).Value = WorksheetFunction.
Transpose(UniqArray)
End Sub
This code works similarly to the previous routine. The points of difference are:
Instead of disabling error trapping, the Exists property of the Dictionary
object is used to decide whether a value has already been added to the
dictionary and needs to be skipped.
Part
I
The dictionary contents are transferred to UniqArray in one go with:
UniqArray = UniqueDic.Items
Instead of transferring the unique items to the worksheet, you can fi ll a combo
box with the values, using a statement like:
UserForm1.ComboBox1.List=UniqArray
Summary: You can extract a list of unique values from a large list that may
contain multiple instances of a given value. The extracted values can be put
into a worksheet range or used to fi ll values in a combo box or list box.
Sources: http://www.mrexcel.com/forum/showthread.php?t=8485, http://www.
mrexcel.com/forum/showthread.php?t=217977, and http://www.mrexcel.com/
forum/showthread.php?t=41643
AUTO-NUMBER RECORDS AND COLUMNS
IN AN EXCEL DATABASE
Challenge: You want to build formulas to automatically serially number records
and column headers in a database to which AutoFilter is applied and in which
selected columns are hidden.
In the database shown in Figures 65 and 66, the records as well as columns
are numbered normally. Figure 66 illustrates how the database appears when
AutoFilter is applied to show records for total marks > 335 and the columns
for Chemistry and Math are hidden. You want to auto-number the records (1
through 7 in this example) and column headers (1 through 5 in this example)
by using formulas.
 
 
Search JabSto ::




Custom Search