How to hide the next 30 rows below the “second” duplicate value in Excel VBA

I searched a lot on this and couldn’t do it by myself so tried to get expert opinion

I would like to hide block of rows based on duplicate values but keeping the first duplicate value and rows visible, meaning :

I have cells B20, B50,B80,B110,B140,etc are filled out automatically based on another sheet entries “no problem”, so the data are not unique “same every time” and may have duplicate value often time, I want to hide the next below 30 rows for the second duplicate, leaving the first block as it is.
Example: in below example for cell value 1111, I would like to keep rows 20 to 49 visible “because it is the first duplicate” but hide the rows 80 to 109 and hide also rows 140 to 169 because they are second and third duplicates and so on.

B20, B50,B80,B110,B140

1111

B20 1111
B21 something
B22 something
.
.
.
B50 2222
B51 something
B52 something
.
.
.
B80 1111
B81 something
B82 something
.
.
.
B110 2222
B111 something
B112 something
.
.
.
B140 1111
B141 something
B142 something
.
.
.
Etc…


I tried many things so far but couldn’t do selective block row hide based on duplicates as the above example. So if you have some time please share your magic.

1 Answer
1

Put this code into the worksheet's private code sheet (right-click worksheet name tab, View Code)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B20,B50,B80,B110,B140")) Is Nothing Then
On Error GoTo safe_exit
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim t As Range, r As Range, b As Boolean
Range("B20").Resize(150, 1).EntireRow.Hidden = False
For Each t In Range("B20,B50,B80,B110,B140")
b = False
For Each r In Range("B20,B50,B80,B110,B140")
If r.Value = t.Value And b Then
r.Resize(30, 1).EntireRow.Hidden = b
ElseIf r.Value = t.Value Then
r.Resize(30, 1).EntireRow.Hidden = b
b = True
End If
Next r
Next t
End If

safe_exit:
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.