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

MPFraser7

New Member
Joined
Dec 14, 2016
Messages
34
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:


000100020003
00040001
00050001
00060002
00010003

<tbody>
</tbody>


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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
How about this
Code:
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.
 
Upvote 0
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?
 
Upvote 0
Try
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
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
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?
 
Upvote 0
How about
Code:
Range("B:E").EntireColumn.Hidden = True
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top