Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Hide rows

  1. #1
    Board Regular
    Join Date
    Feb 2015
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Hide rows

    Hello,

    i have a table which contains a column for years from 2014 - 2016,showing only year no month no date and cells format is General .
    I need a formula to hide the rows containing year in future ( 2020 till 2026 ) when its year 2020 that row should be unhidden and so on.

    Thanks in advance.

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,708
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide rows

    I am confused by your request. Can you show some sample data with an explanation of which data should be hidden. You refer to both columns and rows. Not sure what should be hidden.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.

  3. #3
    Board Regular
    Join Date
    Feb 2015
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide rows

    HI,

    this is how my table looks our

    col a col b col c col d col e col f col g col h coli Col j col k col l col m col n
    year under18 over 2018 baptism new total move deaths active inactive amount payed rest total payed col k / Col m
    2014
    2015
    2016
    2017
    2017 and more rows as above till year 2026

    there will data in all rows, i need to hide data in rows where year is in future so for now all data in years 2020 - 2026 should be hidden, and when its 2020 the data should appear and so on .

    Thanks in advance.
    Last edited by YOUNAN; Apr 20th, 2019 at 10:45 AM.

  4. #4
    Board Regular
    Join Date
    Feb 2015
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide rows

    HI,

    this is how my table looks our

    col a, col b, col c, col d, col e, col f, col g, col h, col i, Col j, col k, col l, col m, col n
    year, under18, over 2018, baptism, new , total, move, deaths, active, inactive, amount payed, rest, total payed, col k / Col m
    2014
    2015
    2016
    2017
    2017 and more rows as above till year 2026

    there will data in all rows, i need to hide data in rows where year is in future so for now all data in years 2020 - 2026 should be hidden, and when its 2020 the data should appear and so on .

    Thanks in advance.

  5. #5
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,623
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Hide rows

    YOUNAN,

    To start off, and, so that we can get it right on the first try:

    Can you post a screenshot of the actual raw data worksheet?

    And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

    To post a small screen shot (NOT a graphic, or, picture, or, PNG file, or, flat text) try one of the following:

    Click on the below link to see How to display your sheet, and, how to install, download, and, use the MrExcel HTML Maker:

    http://www.mrexcel.com/forum/board-a...uidelines.html


    Or, it is always easier to help and test possible solutions if we could work with your actual file.

    Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com.

    Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

    Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

    If the workbook contains confidential information, you could replace it with generic data.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  6. #6
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,996
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Hide rows

    Put this procedure in a standard module:
    Code:
    Sub HideOrUnhideRows()
    Dim c As Range
    Application.ScreenUpdating = False
    ActiveSheet.Cells.EntireRow.Hidden = False
    For Each c In ActiveSheet.Range("A2:A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row)
        If c.Value > Year(Date) Then c.EntireRow.Hidden = True
    Next c
    Application.ScreenUpdating = True
    End Sub
    Put this procedure in Thisworkbook after you change the sheet name (in red) to match your sheet's name:
    Code:
    Private Sub Workbook_Open()
    Sheets("Sheet1").Select
    Call HideOrUnhideRows
    End Sub
    Put this procedure in a sheet module for your sheet:
    Code:
    Private Sub Worksheet_Activate()
    Call HideOrUnhideRows
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,342
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    31 Thread(s)

    Default Re: Hide rows

    Quote Originally Posted by YOUNAN View Post
    there will data in all rows, i need to hide data in rows where year is in future so for now all data in years 2020 - 2026 should be hidden, and when its 2020 the data should appear and so on .
    I find your wording above to be curious. You are not saying you want to hide the rows for years greater than the current year, rather, you are specifically saying you want to hide the data. You probably meant "hide the rows" and, if so, you have a solution from JoeMo which should work for you. However, if you really meant you wanted to leave the data available (by selecting the cells and looking at the Formula Bar) but simply wanted to hide it from view when looking at the worksheet, you could do that with Conditional Formatting. If that is what you want to do, select all your data and then bring up the Conditional Formatting dialog box (Home tab, Styles panel, New Rule from the Conditional Formatting drop down), select "Use a formula to determine which cells to format" and put this formula in the "Format values where this formula is true" field...

    =AND(ROW($A1)>1,$A1>YEAR(NOW()))

    then click the "Format..." button and select "Custom" from the list on the "Number" tab... use this format type pattern...

    ;;;

    that is 3 semi-colons and nothing else. Now OK your way back to the worksheet and your out-of-bound years' data will be invisible (but selectable).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular
    Join Date
    Feb 2015
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide rows

    Hi , and thanks to everyone answered and helped me. below is a picture of my sheet.

    https://www.dropbox.com/s/lq2wu2ajsx...itled.jpg?dl=0

    i need hole rows 2020 - 2026 to be hidden, because now its 2019, when its 2020 to show 2020 and so on. and would do this by formula cause the people who would work on this file and far worse than me in excel.

    Thanks in advance

    Best Regards

  9. #9
    New Member
    Join Date
    Nov 2014
    Location
    Luxembourg
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide rows

    Perhaps if you simply converted your file into a Table and only populate Row 2020 when it is Jan. 1, 2020 would be the cleanest solution. Your formulae will automatically extend down as you add new rows. As this is a task that will happen once per year, a macro seems a bit excessive to merely hide some rows. Just my opinion. The elegant code above also does the job.

  10. #10
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,623
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Hide rows

    Quote Originally Posted by YOUNAN View Post
    Hi , and thanks to everyone answered and helped me. below is a picture of my sheet.

    https://www.dropbox.com/s/lq2wu2ajsx...itled.jpg?dl=0

    i need hole rows 2020 - 2026 to be hidden, because now its 2019, when its 2020 to show 2020 and so on. and would do this by formula cause the people who would work on this file and far worse than me in excel.

    Thanks in advance

    Best Regards
    YOUNAN,

    We can not work with your jpg file.

    Please see my reply #5.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •