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

#### Loin75

##### Active Member
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.

Code:
``````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...

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Joe4

What version of Excel are you using?

#### joemeshuggah

##### Board Regular
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:
Code:
``````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

##### Active Member
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.

Replies
11
Views
839
Replies
3
Views
730
Replies
11
Views
941
Replies
4
Views
2K
Replies
7
Views
221

1,191,587
Messages
5,987,510
Members
440,098
Latest member
MickyMouse123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back