Create a List of the unique values in a column
Note: Do not add [Blanks] to the list:
Use the BasicTemplate and the GetLastCell Function
These are the key components...
Use the BasicTemplate and the GetLastCell Function
These are the key components...
Set the [Worksheet], [End Row], [End Column] values
'OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
Ws1 = Worksheets("Filter").Index
iRe1 = Range(GetLastCell(Ws1)).Row
iCe1 = Range(GetLastCell(Ws1)).Column
'OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
Ws1 = Worksheets("Filter").Index
iRe1 = Range(GetLastCell(Ws1)).Row
iCe1 = Range(GetLastCell(Ws1)).Column
'OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
Get User Input to determine which column contains the list of values
'===================================================================================================
iColReview = Application.InputBox(Prompt:="What[Column] contains the [Key]?", Title:="Specify Column by Clicking on ANY Cell.", Default:=ActiveCell.Address, Type:=8).Column
'===================================================================================================
'OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
Note: Remember to add some type of validation/error trapping
If iColReview = 0 Or iColDump = 0 Then GoTo gHeaderIssueAbandonAllHope
'OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
iColReview = Application.InputBox(Prompt:="What[Column] contains the [Key]?", Title:="Specify Column by Clicking on ANY Cell.", Default:=ActiveCell.Address, Type:=8).Column
'===================================================================================================
'OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
Note: Remember to add some type of validation/error trapping
If iColReview = 0 Or iColDump = 0 Then GoTo gHeaderIssueAbandonAllHope
'OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
Create the array that contains ALL the Worksheet Data
'===================================================================================================
Worksheets(Ws1).Select
aData = Range(Worksheets(Ws1).Cells(1, 1).Address, Worksheets(Ws1).Cells(iRe1, iCe1).Address)
'===================================================================================================
Worksheets(Ws1).Select
aData = Range(Worksheets(Ws1).Cells(1, 1).Address, Worksheets(Ws1).Cells(iRe1, iCe1).Address)
'===================================================================================================
Run the loop that reviews each cell in the column
(Don't forget: DoEvents and Application.StatusBar)
'===================================================================================================
'Prepare the string:
sKeyList = "^"
'===================================================================================================
For iLoop = LBound(aData) + 1 To UBound(aData)
DoEvents
If Right(iLoop, 2) = "00" Then Application.StatusBar = iLoop & " of " & UBound(aData)
'Skip [blanks]
If aData(iLoop, iColReview) = "" Then GoTo gMoveToNextLoop
'Check to see if value is already in the list - if not, add it
If InStr(1, sKeyList, "^" & aData(iLoop, iColReview) & "^", vbTextCompare) = 0 Then sKeyList = sKeyList & aData(iLoop, iColReview) & "^"
gMoveToNextLoop:
Next iLoop
'===================================================================================================
'Prepare the string:
sKeyList = "^"
'===================================================================================================
For iLoop = LBound(aData) + 1 To UBound(aData)
DoEvents
If Right(iLoop, 2) = "00" Then Application.StatusBar = iLoop & " of " & UBound(aData)
'Skip [blanks]
If aData(iLoop, iColReview) = "" Then GoTo gMoveToNextLoop
'Check to see if value is already in the list - if not, add it
If InStr(1, sKeyList, "^" & aData(iLoop, iColReview) & "^", vbTextCompare) = 0 Then sKeyList = sKeyList & aData(iLoop, iColReview) & "^"
gMoveToNextLoop:
Next iLoop
'===================================================================================================
Alternate Method: Preserve the [Delimited Position] vs [Row] relationship
NOTE: This may NOT be a Unique List (Just a list)
'===================================================================================================
'Prepare the string:
sKeyList = "^"
'===================================================================================================
For iLoop = LBound(aData) + 1 To UBound(aData)
DoEvents
If Right(iLoop, 2) = "00" Then Application.StatusBar = iLoop & " of " & UBound(aData)
'Add Each Value so that the delimited position matches the Row it was located on
If InStr(1, sKeyList, "^" & aData(iLoop, iColReview) & "^", vbTextCompare) = 0 Then sKeyList = sKeyList & aData(iLoop, iColReview) & "^"
gMoveToNextLoop:
Next iLoop
'===================================================================================================
'Prepare the string:
sKeyList = "^"
'===================================================================================================
For iLoop = LBound(aData) + 1 To UBound(aData)
DoEvents
If Right(iLoop, 2) = "00" Then Application.StatusBar = iLoop & " of " & UBound(aData)
'Add Each Value so that the delimited position matches the Row it was located on
If InStr(1, sKeyList, "^" & aData(iLoop, iColReview) & "^", vbTextCompare) = 0 Then sKeyList = sKeyList & aData(iLoop, iColReview) & "^"
gMoveToNextLoop:
Next iLoop
'===================================================================================================