Dear,

A new challange on the end of this year..... :)
Perhaps somebody like to solve and help me out.

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

Something like...... Column N, From 2nd row till last, If visible cell contains "x" and below visible cell contains "x" or below visible cell is empty, then 'hide row'. Next row.

regards, Johan

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

Hoi Claus, Thx !!

I'm sorry but,..... it doesn't work as wished.

The selection is oke and then I've got visible rows with 'data-B' and a lot of rows with 'x'. None of the rows with 'x' (when there is a visible row below with also an 'x' in column N) is hidden by the macro.

I see that it isn't work because in column N is also other data registered then 'Data-B' and 'x'. When I delete this data then it works oke.
Strange.... because I can't delete this data. This is the data in the rows that are the hidden rows (those are the ones that are not selected in the filtering).

regards, Johan

Hi Johan,

Am Mon, 30 Dec 2019 06:35:51 -0800 (PST) schrieb JS SL:

The selection is oke and then I've got visible rows with 'data-B' and a lot of rows with 'x'. None of the rows with 'x' (when there is a visible row below with also an 'x' in column N) is hidden by the macro.

I see that it isn't work because in column N is also other data registered then 'Data-B' and 'x'. When I delete this data then it works oke.
Strange.... because I can't delete this data. This is the data in the rows that are the hidden rows (those are the ones that are not selected in the filtering).

try:

Sub Test()
Dim rngC As Range
Dim LRow As Long, n As Long
Dim varRows() As Variant

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 Each rngC In .Range("N2:N" & LRow).SpecialCells(xlCellTypeVisible)
ReDim Preserve varRows(n)
varRows(n) = rngC.Row
n = n + 1
Next
For n = LBound(varRows) To UBound(varRows) - 1
If .Cells(varRows(n), "N") = "x" And .Cells(varRows(n + 1), "N") < "data-B" Then
.Rows(varRows(n)).Hidden = True
End If
Next
If .Cells(LRow, "N") = "x" Then .Rows(LRow).Hidden = True
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Yes, super !! (And a HappyNewYear to you and all other users !),

Can you please make an update to select <"data-B" on another way.

Actual it is:
If .Cells(varRows(n), "N") = "x" And .Cells(varRows(n + 1), "N") < "data-B" Then

It should be nice if the part "data-B" can refer to 'Sheet2, ColumnA' where standing a few keywords in A2, A3, A4, etc..
For the part < it should look in those sheet/column and only fullfill the code for HideRow if the text in column N isn't in Sheet2/ColumnA.
On this way it make it flexible to hide rows based on several written codes in the other sheet.

You get then something like;

If .Cells(varRows(n), "N") = "x" And .Cells(varRows(n + 1), "N") < [data in sheet2 columnA] Then....

regards, Johan

Hi Johan,

Am Wed, 1 Jan 2020 03:56:12 -0800 (PST) schrieb JS SL:

It should be nice if the part "data-B" can refer to 'Sheet2, ColumnA' where standing a few keywords in A2, A3, A4, etc..
For the part < it should look in those sheet/column and only fullfill the code for HideRow if the text in column N isn't in Sheet2/ColumnA.
On this way it make it flexible to hide rows based on several written codes in the other sheet.

do you filter for all entries in sheet2 column A? Or only for one of
these entries?


Regards
Claus B.
--
Windows10
Office 2016

Hello Claus,

The ones in Sheet2/ColumnA are always the updated unique ones that I selected by filtering.
When made the choice of filtering (could be one or more) I set the unique data of the filtering (except the "x" only) updated in Sheet2/ColumnA.

Then I want to run the macro that you made :)

regards, Johan (and thx!)

Hi Johan,

Am Wed, 1 Jan 2020 20:44:33 -0800 (PST) schrieb JS SL:

The ones in Sheet2/ColumnA are always the updated unique ones that I selected by filtering.
When made the choice of filtering (could be one or more) I set the unique data of the filtering (except the "x" only) updated in Sheet2/ColumnA.

then try:

Sub Test()
Dim rngC As Range
Dim LRow As Long, n As Long
Dim varRows() As Variant, varFilter As Variant
Dim strFilter As String

With Sheets("Sheet2")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
varFilter = .Range("A1:A" & LRow)
strFilter = Join(Application.Transpose(varFilter), ",")
End With

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, "N").End(xlUp).Row
For Each rngC In .Range("N2:N" &
LRow).SpecialCells(xlCellTypeVisible)
ReDim Preserve varRows(n)
varRows(n) = rngC.Row
n = n + 1
Next
For n = LBound(varRows) To UBound(varRows) - 1
If .Cells(varRows(n), "N") = "x" And _
InStr(strFilter, .Cells(varRows(n + 1), "N")) = 0 Then
.Rows(varRows(n)).Hidden = True
End If
Next
If .Cells(LRow, "N") = "x" Then .Rows(LRow).Hidden = True
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Claus,

Really amazing !!!!!!!!
Super Thanks !!

regards, Johan