Autofiltering columns

[cpis0002]

I have a spreadsheet template with a range of years from 1997-2020. I want an autofiltering tool so when the year is greater than the current year (current year is named: Year_End) it will be hidden...

That is: like when you have a long list of items with quantities, you can autofilter the rows to only show items with quanitities greater than 0...

I want the same thing for columns, any idea?

 

[stanleydgromjr]

cpis0002,

Post a screenshot of your sheet(s) with Colo's HTML Maker.

[url]http://www.puremis.net/excel/downloads.shtml
[/url]

Have a great day,
Stan

 

[cpis0002]

The sheet is quite big, about 600 rows and 50 columns :S

Theres nothing much to understand about it... to make it simler:

I have Row 4 with amounts 0 or 1 throught columns C to AA.
These are made up of the formula IF(year>Year_End,0,1)

So then I need something to remove all the columns with the amount 0 in row 4... That is: the year is after the current year (Year_End).

 

[stanleydgromjr]

cpis0002,

I have a solution using the Worksheet_Change Event, and the range name 'Year_End'.

Hide Column if row 4 value equals to zero - with Worksheet_Change Event.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1		<---Year_End
2
3			1997	1998	1999	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021
4			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5
Sheet1

 

[stanleydgromjr]

Next part:

If you make a change to cell A1, range name 'Year_End', say to 2011, and press the ENTER key:

Hide Column if row 4 value equals to zero - with Worksheet_Change Event.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	2011	<---Year_End
2
3			1997	1998	1999	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011
4			1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
5
Sheet1

 

[stanleydgromjr]

One more screenshot:

If you enter '3000' into the range name 'Year_End' and press the 'ENTER' key:

Hide Column if row 4 value equals to zero - with Worksheet_Change Event.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	3000	<---Year_End
2
3			1997	1998	1999	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021
4			1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
5
Sheet1

 

[stanleydgromjr]

And the code:

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into VBAProject, Microsoft Excel Objects, Sheet1(Sheet1): or the sheetname where the data is

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("Year_End")) Is Nothing Then
        Exit Sub
    End If

    If Target.Value < 1997 Then
        'Do nothing
        Exit Sub
    End If

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim lngColumnNumber As Long

    For lngColumnNumber = 27 To 3 Step -1
        If Cells(4, lngColumnNumber).Value < 1 Then
            Cells(4, lngColumnNumber).EntireColumn.Hidden = True
        Else
            Cells(4, lngColumnNumber).EntireColumn.Hidden = False
        End If
    Next lngColumnNumber

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

Make some changes to the range name 'Year_End'.

Have a great day,
Stan