Hide rows

[YOUNAN]

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.

 

[alansidman]

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.

 

[YOUNAN]

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.

 

[YOUNAN]

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.

 

[hiker95]

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.

 

[JoeMo]

Put this procedure in a standard module:

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:

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Call HideOrUnhideRows
End Sub

Put this procedure in a sheet module for your sheet:

Private Sub Worksheet_Activate()
Call HideOrUnhideRows
End Sub

 

[Rick Rothstein]

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).

 

[YOUNAN]

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

https://www.dropbox.com/s/lq2wu2ajsxuivj8/Untitled.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

 

[higrm]

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.

 

[hiker95]

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

https://www.dropbox.com/s/lq2wu2ajsxuivj8/Untitled.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.