Microsoft Office Tutorials and References
In Depth Information
Generating a Unique List
'1. Have a hardcoded range¶
'Set Rng = Range("A1:C100")¶
'2. Use the current selection¶
'Set Rng = ActiveWindow.RangeSelection¶
'3. Ask the user for a range¶
On Error Resume Next¶
Set Rng = Application.InputBox( _¶
Prompt:="Select the range to filter (Including headers)", _¶
Title:="Select Range", Type:=8)¶
On Error GoTo 0¶
'Make sure a valid range is used¶
If Rng Is Nothing Then¶
Exit Sub¶
End If¶
'Create the new worksheet after the selected one¶
Set WS = Rng.Worksheet.Parent.Worksheets.Add( _¶
'Filter the items (Use Advanced Filter for this)¶
Rng.AdvancedFilter Action:=xlFilterCopy, _¶
CopyToRange:=WS.Range("A1"), Unique:=True¶
End Sub¶
There is one variable that can be changed in the 'Change the following
variables' section: the range. Inside the code there are three examples of how to
set this variable:
1. Use a predefined range.
2. Use the currently selected range.
3. Ask the user to input a range (this is the option enabled in the code as
To enable one of the other two, place an apostrophe in front of the code lines for
the one currently in use, and then remove the apostrophe in front of the code
line for the option desired.
Search JabSto ::

Custom Search