View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
Default After made a filter selection, hide to select visible rows based on the registration in the visible cell related to the visible row above

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