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:
<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.
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:
0001 | 0002 | 0003 | ||
0004 | 0001 | |||
0005 | 0001 | |||
0006 | 0002 | |||
0001 | 0003 |
<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.