Search for and hide many rows based on value of 2 cells

[Loin75]

Hi - the title is probably not very accurate, sorry.


I have a spreadsheet with the following:
Column A = Client Name
Column B = Postcode
Column C = Numerical Values


John Smith (for example) may be entered in many rows. Lets say he has 10 rows with respective data.
Column C should be numerical, but there are "?" entered for some values that we do not know.


My goal - If any one of John's rows has a ? entered into Column C, then I want to hide all of John's rows.


To make this slightly more complicated, we need to be able to match John Smith with his postcode to make sure we are not hiding rows belonging to a different John Smith. (we are talking 20000 rows of client data).


I have this code as my starter, but do not have the knowledge to include the correct search mechanism, if it is at all possible.

Option Explicit
 
Dim c As Range, rng As Range
 
Sub Hide()
     
    Set rng = Range("C2", Range("C10000").End(xlUp))
     
    For Each c In rng
         
        If c.Value = "?" Then
             
            c.EntireRow.Hidden = True
             
        End If
         
    Next cel
     
End Sub

Many thanks for any help...

 

[Joe4]

What version of Excel are you using?

 

[Loin75]

What version of Excel are you using?

2010

Cheers

 

[joemeshuggah]

maybe something like this...

Sub Hide()
rng = Range("C10000").End(xlUp).Row
Dim MyArray()
ReDim MyArray(1 To rng - 1, 1 To 2)
Z = 0
For i = 2 To rng
If Range("C" & i).Value = "?" Then
Z = Z + 1
MyArray(Z, 1) = Range("A" & i).Value
MyArray(Z, 2) = Range("B" & i).Value
End If
Next i
For j = 2 To rng
x = Range("A" & j).Value
y = Range("B" & j).Value
For k = 1 To Z
If x = MyArray(k, 1) And y = MyArray(k, 2) Then
Range("A" & j).EntireRow.Hidden = True
End If
Next k
Next j

End Sub

 

[Joe4]

Here's the code I came up with:

Sub MyHide()


    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRow As Long
    Dim RangeA As Range
    Dim RangeB As Range
    Dim RangeC As Range
    Dim myCount As Long
    
'   Set row that data starts on
    myStartRow = 2
        
'   Find last row with data in column A
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Set ranges
    Set RangeA = Range("A" & myStartRow & ":A" & myLastRow)
    Set RangeB = Range("B" & myStartRow & ":B" & myLastRow)
    Set RangeC = Range("C" & myStartRow & ":C" & myLastRow)
    
    Application.ScreenUpdating = False
    
'   Loop through all rows
    For myRow = myStartRow To myLastRow
'       Count number of question marks in all data for criteria on particular row
        myCount = Application.WorksheetFunction.CountIfs(RangeA, Cells(myRow, "A"), RangeB, Cells(myRow, "B"), RangeC, "?")
'       If any found, hide row
        If myCount > 0 Then Rows(myRow).EntireRow.Hidden = True
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[Loin75]

Thanks Joe4 - that code is perfect. I am going to study it for a while, you legend!!

Thanks also to Joemeshuggah

 

[Joe4]

Thanks Joe4 - that code is perfect. I am going to study it for a while, you legend!!

You're welcome. If you have any specific questions about it, feel free to ask them.