Hide Rows Based On A List Of Names

[JLouis]

Hello. I've been trying to write a vba code that will reference a list of names on one sheet, find the name in a list on another sheet, and hide the row the name appears in on the 2nd sheet. I've gotten to where I can do so for a single name but can't get the language right for the loop, lookup.

I currently have these two subs in the workbook. Looks like a combination of the two:

Sub SearchMe()
Application.ScreenUpdating = False

    Dim rng1 As Range
    Dim strSearch As String
    strSearch = ActiveCell.Value
    Application.ScreenUpdating = False
    Set rng1 = Range("o156:O159").Find(strSearch, , xlValues, xlWhole)
    If Not rng1 Is Nothing Then
        rng1.ClearContents
        Range("p12").Select
    Else
        MsgBox "Name Is Not In The List."
    End If
    Application.ScreenUpdating = True
End Sub

Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual
UNPROTECT
  ActiveSheet.Select
For Each C In Range("d50:d500")
    If C.Value = 0 Or C.Value = "" Then Rows(C.Row).Hidden = True
Next
 
   ''  Rows("132:167").EntireRow.Hidden = False
     PROTECT
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Any help is appreciated.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[JLouis]

Thanks for the reply.

I have XL2BB but didn't see the need to post 2 sheets with a list of names on each of them. What part of my post seems vaque or not clear? If it would help with assistance I certainly can do it.

 

[mumps]

a list of names on one sheet, find the name in a list on another sheet

This seems to say that you have two sheets. It would be helpful to see both sheets.

 

[JLouis]

Here you go.

Book4
	A	B	C	D	E	F	G	H
1	Worksheet2
2
3	blue		Look up these values on Sheet1 and hide the row it appears in.
4	red
5
6
Sheet2

Book4
	A	B	C	D
1	Worksheet1
2
3	blue		Hide this row.
4	green
5	yellow
6	red		Hide this row.
7	purple
8
Sheet1

 

[mumps]

Try:

Sub HideRows()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, i As Long, dic As Object
    Set srcWS = Sheets("Sheet2")
    Set desWS = Sheets("Sheet1")
    v1 = desWS.Range("A1", desWS.Range("A" & Rows.Count).End(xlUp)).Value
    v2 = srcWS.Range("A1", srcWS.Range("A" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            dic.Add v1(i, 1), i
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        If dic.exists(v2(i, 1)) Then
            desWS.Rows(dic(v2(i, 1))).Hidden = True
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[JLouis]

That is some complex code. I put it in my simple sample WB, and it worked perfectly. When I modified it to suit my workbook it didn't work. Could you take a peek and see where i messed up?

Thank you so much for spending your time on this. Quite amazing actually. Here's my modifications:

Sub HidePlayers()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, i As Long, dic As Object
    Set srcWS = Sheets("hidden names")
    Set desWS = Sheets("name list")
    v1 = desWS.Range("j8", desWS.Range("j" & Rows.Count).End(xlUp)).Value
    v2 = srcWS.Range("h2", srcWS.Range("h" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            dic.Add v1(i, 1), i
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        If dic.exists(v2(i, 1)) Then
            desWS.Rows(dic(v2(i, 1))).Hidden = True
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Set desWS = Sheets("hidden names")
Set srcWS = Sheets("name list")

dic.Add v1(i, 1), i +1

Try changing the corresponding lines of code in your modified macro to what I’ve listed above.

 

[JLouis]

Nevermind, I found my error. Excel didn't appreciate me changing the column range from 1 to another number. Once I changed it back it worked perfectly. You've been an excellent source of assistance. Thank you again very much.

Louis

 

[mumps]

You are very welcome.