View original (French)

Automatically hide rows in multiple sheets

Solved
Stephxx -  
Stephxx Posted messages 26 Status Member -
Hello everyone,

I created a two-page workbook. The first page contains a list of items in column A with their parameters in columns B and C. Users of the file apply their filters on columns B and C to keep only certain items. As a result, some items, meaning some rows, are hidden.
I would like the same items that are displayed in column A of Sheet1 to appear in column A of Sheet2, meaning all the rows that are not hidden. Any ideas?

Details:

Sheet1

A B C
Row1 Item

Row2 toto

Row4 tata

Row9 tonton

Rows 3, 5, 6, 7, 8 are thus hidden by filters on columns B and C.

I want to create Sheet2 with:
Sheet2
A
Row1 Item

Row2 toto

Row3 tata

Row4 tonton

Preferably with formulas, simpler for my users. With a macro, it should trigger as soon as a filter is applied but also if a user manually hides a row in Sheet1.

Thanks in advance.

Configuration: Mac OS X (10.10.4) / Safari 8.0.7

2 answers

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Hello

    By formula, I don't think it's possible to test if a row is hidden

    By macro, we can create a macro placed in the Worksheet of the second sheet that activates as soon as we select sheet 2 

    Private Sub Worksheet_Activate() Dim Row As Long 'last non-empty row sheet 1 Row = Sheets(1).Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row ' Loop through the rows sheet 1 For n = 1 To Row ' if the row is not hidden If Sheets(1).Rows(n).Hidden = False Then 'increment variable i for the row i = i + 1 'Loop through columns 1 to 3 of sheet 1 (to be adjusted if necessary) For c = 1 To 3 ' Copy the cells to sheet 2 Sheets(2).Cells(i, c) = Sheets(1).Cells(n, c) Next c End If Next n End Sub


    To place the macro in Worksheet sheet 2:
    ALT + F11 to open the VBA editor
    Double-click to select Sheet 2 in the hierarchy
    Copy-paste the macro into the page
    Close the editor

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    1
    1. Stephxx Posted messages 26 Status Member
       
      Merci Via, this code works perfectly.

      I am trying to evolve it and I ended up with the following code that allows me to hide the lines on sheet 2 in the same way as those on sheet 1. 

      Sub Macro1()
      Dim Ligne As Long
      i = 5
      Ligne = Sheets(1).Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
      For n = 5 To Ligne
      If Sheets("Données").Rows(n).Hidden = True Then
       Sheets("Détail").Rows(i).Hidden = True
       i = i + 1
      Else: Sheets("Détail").Rows(i).Hidden = False
      i = i + 1
      End If
      Next n
      End Sub


      I am now looking to obtain the total price of the order in a cell, i.e.: multiply the number of items ordered (sheet2 colB) by the unit price (sheet1 colC). Initially, I obtained this total price with the SUMPRODUCT function: "=SOMMEPROD(Feuil2!B5:B2000;Feuil1!$C5:$C2000)"

      Do you know of an equivalent function to SUMPRODUCT that does not take into account hidden rows? I have explored SUBTOTAL but without success.
      0
    2. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759 > Stephxx Posted messages 26 Status Member
       
      It is indeed SUBTOTAL that should be used; for example, to get the total of the unhidden rows in column C:
      =SUBTOTAL(9;C:C)
      0
    3. Stephxx Posted messages 26 Status Member
       
      The function SUBTOTAL(9, ...) gives the sum of values, I am looking for the equivalent of SUMPRODUCT that gives the sum of the product of two columns (C1*B1+C2*B2+C3*B3+ ...).

      PS: I'm detailing just in case, but I suspect this function is well known :)
      0
    4. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759 > Stephxx Posted messages 26 Status Member
       
      It is necessary to base the filter on the column
      For example, if the filter is an X in column D and the prices are in column C
      =SUMPRODUCT((C2:C2000)*(D2:D2000="X"))
      0
    5. Stephxx Posted messages 26 Status Member
       
      Thank you, Via. I had seen this solution, but end users will filter based on multiple undetermined criteria. We cannot rely on this solution.
      0
  2. Stephxx Posted messages 26 Status Member
     
    The subject is resolved, unfortunately, I wasn't logged in when I posted it, it's impossible for me to mark it as such. ARTICLE RESOLVED, thank you.
    0