Hi Johan,
Am Mon, 30 Dec 2019 02:19:13 -0800 (PST) schrieb
JS SL:
I've got a list with data like;
Column N:
row2 x
Row3 x
Row4 data-A
Row5 data-A
Row6 data-B
Row7 x
Row8 data-B
Row9 x
Row10 x
Row11 data-A
Row12 data-A
Row13 data-A
Row14 data-B
Row15 data-A
Row16 x
Row17 x
Row18 x
Now I make a filterselection in column N for "Data-B" and "x"
(I need "x" also because its a special headingrow for selected 'Data-' fields).
So I get after the selection;
Column N:
row2 x
Row3 x
Row6 data-B
Row7 x
Row8 data-B
Row9 x
Row10 x
Row14 data-B
Row16 x
Row17 x
Row18 x
As you can see, the rows with other data then "Data-B" and "x" are hidden rows now.
The question now is to get a macro that will hide from those 'visible rows' the rows with an "x" in column N if the visible row direct below it, has also a "x" or the row below is empty (that means for the last rows)
Then you get;
Column N:
Row3 x
Row6 data-B
Row7 x
Row8 data-B
Row10 x
Row14 data-B
try:
Sub Test()
Dim rngC As Range
Dim LRow As Long, i As Long
With ActiveSheet
LRow = .Cells(.Rows.Count, "N").End(xlUp).Row
With .Range("N1")
.AutoFilter Field:=1, Criteria1:="=data-B", _
Operator:=xlOr, Criteria2:="=x"
End With
For i = LRow To 2 Step -1
If .Cells(i, "N") = "x" Then
If .Cells(i + 1, "N") = "x" Or IsEmpty(.Cells(i + 1, "N")) Then
.Rows(i).Hidden = True
End If
End If
Next
End With
End Sub
Regards
Claus B.
--
Windows10
Office 2016