Hi John,
Thank you for this code.
I have tested this on the actual workbook and it seems to do exactly what I need.
There was 1 more question. Some of the values are not just a number v1234 for example, this shows up fine, but there are occasional days where an actual number value 987 (number) may populate as 987 (text) number. I have noticed that your VBA list this as a separate value.
Is there a way, in the loop, to convert the text 987 to a number but still leave v1234 as a legit unique value?
I messed with a quick excel formula to do [cell]+1 but that gives a #value error on the v1234 numbers so that was a bust.
I have added some comments to the code, since it will be another user will be relying on this code going forward and I want to make sure they understand what is going on.
Can you confirm if my comments are accurate?
Thank you very much. Once you respond I'll mark this as solved.
Commented Code:
Option Explicit
Sub Master_List()
' declare variables
Dim Current As Worksheet
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range
Dim i As Integer
Dim n As Long
Dim txt As String
Dim ar As Variant
Dim arr(1 To 100000, 1 To 1) ' if you think you will have more than 100,000 in the master list, update this variable
' this turns off all the excel visually showing you what it does
Application.ScreenUpdating = False
' CreateObject("scripting.dictionary") is a nifty way to create an array that already has unique keys
' and allows for easier checking to see if the value already exists
' very easily helps with creating unique lists
With CreateObject("scripting.dictionary")
' looping through the worksheets that start with "Week"
For Each Current In Worksheets
If Current.Name Like "Week*" Then
' next this looks column B starting at B2 then goes down to find the last filled in record
' then stores it as the "rng" variable then adds to the "ar" array variable
' if the range on the WEEK sheets changes from B2 then up date this
Current.Activate
Set rng = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
ar = rng
' this is the check to see if the value already exists while it is updating the "ar" array variable
For i = 1 To UBound(ar, 1)
txt = ar(i, 1)
If Not .Exists(txt) Then
n = n + 1
.Add txt, n
arr(n, 1) = ar(i, 1)
End If
Next i
End If
Next Current
End With
' if you "MasterList" worksheet name changes then update this
Set ws2 = Worksheets("MasterList")
' "[B2]" and "B2:B" is the cell you want your unique list to be displayed
With ws2
.[b2].Resize(n) = arr
Set rng = .Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
rng.Sort Key1:=.Range("B1"), Order1:=xlAscending
End With
ws2.Activate
Application.ScreenUpdating = True
End Sub
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks