i am a student currently making a VBA project for one of my teachers. He wants me to sort through names from different schools to hide them.
currently, this is the code i have at hand. What happens is that the code will sort through a specific column, column G of the active workbook i am running it on, on sheet("Default"), whilst there is a list of names that i would like hidden if it appears in the "Default" sheet that is located on the sheet("Sheet1"). A few other things is that Column G might have duplicates of names that need to be hidden as well, with some of them being capitalized and non-capitalized. Any names that are in Column G of "Default" sheet must be hidden if they match any names in Column A of "Sheet1"
Sub FilterNameDuplicate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Rem Unhide all the rows
Worksheets("Default").UsedRange.EntireRow.Hidden = False
Dim Keys As Collection
Set Keys = GKeys
Dim Key As String
Dim Target As Range
With Worksheets("Default")
Set Target = Intersect(.UsedRange, .Columns("G"))
End With
If Target Is Nothing Then
MsgBox "Invalid Range"
Exit Sub
End If
Dim Cell As Range
For Each Cell In Target
Key = (Cell.Value)
On Error Resume Next
Key = Keys(Key)
Cell.EntireRow.Hidden = (Err.Number = 0)
On Error GoTo 0
Next
Rem We no longer need to turn ScreenUpdating back on
Application.Calculation = xlCalculationAutomatic
MsgBox "Done!"
End Sub
Function GKeys() As Collection
Set GKeys = New Collection
Dim Key As String
Dim Data As Variant
Data = Worksheets("Sheet1").Range("A1").CurrentRegion.Columns(1).Value
Dim r As Long
For r = 1 To UBound(Data)
Key = UCase(Data(r, 1))
On Error Resume Next
GKeys.Add Key:=Key, Item:=""
On Error GoTo 0
Next
End Function
The code has been optimized and remade by a fellow member on stack(which i am very grateful for!), and it does its job with only one issue. According to him, the code will use a collection to store the names that need to be hidden, aka column a of "sheet1", which will then allow the code to compare in column g before hiding those that match. This speeds up the macro very well, but ends up preventing me from adding anything to the collection for some weird problem. Whenever i add new names to the Sheet1 column, it does not include itself into the collection, therefore causing it not to be hidden.
sorry for the really long question and code, i am very thankful to any answers suggested :D
PS: really unfortunately, i only got to posting this question on a friday and will not be in for the weekend(intern). Sorry for any late replies in advance.
