Loop through rows and hide rows where it's not a match

[MPFraser7]

The following code allows me to right click on a cell in column A and loop through 100 columns to hide them if there's no value in the corresponding cell:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 Then Exit Sub
Cancel = True
Dim i As Integer
For i = 1 To 10
If IsEmpty(ActiveCell.Offset(0, i)) Then
Columns(i + 1).Hidden = True
Else
Columns(i + 1).Hidden = False
End If
Next i
End Sub

I want to use the same logic with a little twist. I want to right click on Row1, anywhere between column C:AAA, loop through rows 2:100 in Column B and hide all rows where the value in the right clicked cell <> the value in Column B Row 2:100. I'm not sure if this is clear so here's an example:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]0001[/TD]
[TD]0002[/TD]
[TD]0003[/TD]
[/TR]
[TR]
[TD]0004[/TD]
[TD]0001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0005[/TD]
[TD]0001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0006[/TD]
[TD]0002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]0003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If I right click on C1, I want to hide Rows 4 and 5, since C1 = B2 and B3. If I right click on D1, I want to hide Rows 2, 3 and 5, since D1 = B4. Instead of just searching if the cells contains a value like in the original code, it would need to search for an exact match. Please ask question if I'm not clear. Thanks everyone.

 

[Fluff]

How about this

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 Then
      Cancel = True
      Range("A:CV").EntireColumn.Hidden = False
      On Error Resume Next
      Range("A" & Target.Row).Resize(, 100).SpecialCells(xlBlanks).EntireColumn.Hidden = True
      On Error GoTo 0
   End If
   If Target.Row = 1 Then
      Cancel = True
      With Range("B2:B100")
         .EntireRow.Hidden = False
         .Replace Target.Value, True, xlWhole, , True, , False, False
         On Error Resume Next
         .SpecialCells(xlConstants, xlLogical).EntireRow.Hidden = True
         On Error GoTo 0
         .Replace True, Target.Value, xlWhole, , True, , False, False
      End With
   End If
   
End Sub

Depending on the format of your cells, the second part may not work.

 

[MPFraser7]

I changed the "1" to "0" on line 3 and changed the "True" "False" on line 13 and 16 and it works. Thank you so much.

One last thing, is it possible to unhide all columns and rows by right clicking on cell A1 or B1 within the same code?

 

[Fluff]

Try

   If Target.CountLarge > 1 Then Exit Sub
   If Target = Range("A1") Then
      Cancel = True
      With Me.UsedRange
         .EntireRow.Hidden = False
         .EntireColumn.Hidden = False
      End With
      Exit Sub
   End If

 

[MPFraser7]

Thanks, it works!

 

[Fluff]

Glad to help & thanks for the feedback

 

[MPFraser7]

Using the last bit of code:

If Target.CountLarge > 1 Then Exit Sub
If Target = Range("A1") Then
Cancel = True
With Me.UsedRange
.EntireRow.Hidden = False
.EntireColumn.Hidden = False
End With
Exit Sub
End If

How do I change it from unhiding all columns and rows to hiding columns B:E?

 

[Fluff]

How about

Range("B:E").EntireColumn.Hidden = True

 

[MPFraser7]

Thanks again, hopefully one day I'll get to learn VBA.

 

[Fluff]

Your welcome.