Hide rows

YOUNAN

Board Regular
Joined
Feb 10, 2015
Messages
94
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.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,440
Office Version
2019
Platform
Windows
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

Board Regular
Joined
Feb 10, 2015
Messages
94
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:

YOUNAN

Board Regular
Joined
Feb 10, 2015
Messages
94
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,757
Put this procedure in a standard module:
Rich (BB 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:
Rich (BB code):
Private Sub Workbook_Open()
Sheets("Sheet1").Select
Call HideOrUnhideRows
End Sub
Put this procedure in a sheet module for your sheet:
Rich (BB code):
Private Sub Worksheet_Activate()
Call HideOrUnhideRows
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,821
Office Version
2010
Platform
Windows
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

Board Regular
Joined
Feb 10, 2015
Messages
94
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

New Member
Joined
Nov 5, 2014
Messages
47
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,396
Messages
5,450,151
Members
405,589
Latest member
shakenblake

This Week's Hot Topics

Top