Hide rows

YOUNAN

Board Regular
Joined
Feb 10, 2015
Messages
101
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top