Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 30th 19, 10:19 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 49
Default After made a filter selection, hide to select visible rows based onthe registration in the visible cell related to the visible row above

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






  #2   Report Post  
Old December 30th 19, 01:02 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,787
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 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
  #3   Report Post  
Old December 30th 19, 02:35 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 49
Default After made a filter selection, hide to select visible rows basedon the registration in the visible cell related to the visible row above

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
  #4   Report Post  
Old December 30th 19, 09:57 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,787
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 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
  #5   Report Post  
Old January 1st 20, 11:56 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 49
Default After made a filter selection, hide to select visible rows basedon the registration in the visible cell related to the visible row above

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


  #6   Report Post  
Old January 1st 20, 09:45 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,787
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 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
  #7   Report Post  
Old January 2nd 20, 04:44 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 49
Default After made a filter selection, hide to select visible rows basedon the registration in the visible cell related to the visible row above

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!)
  #8   Report Post  
Old January 2nd 20, 03:41 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,787
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
  #9   Report Post  
Old January 2nd 20, 05:57 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 49
Default After made a filter selection, hide to select visible rows basedon the registration in the visible cell related to the visible row above

Claus,

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

regards, Johan


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select first 5 visible rows Paul Brown[_2_] Excel Programming 2 January 5th 08 03:19 PM
hide zero rows / shadwe every other visible Steve[_4_] Excel Programming 1 October 10th 07 07:26 PM
Excel sheet: selection of cell or rows not visible. Hisoul Excel Worksheet Functions 2 May 16th 07 09:06 PM
Hide a visible rows Wanna Learn Excel Discussion (Misc queries) 5 February 15th 07 08:23 PM
how do i select all the visible cells only when using auto filter shaurabh Excel Discussion (Misc queries) 4 June 25th 06 07:32 AM


All times are GMT +1. The time now is 07:03 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017