Automatically hide/unhide rows

[bbugs73]

I have a simple spreadsheet that contains a field where a user will populate a number of months. Below that, there is a row that represents each month. If the user enters 24, which is the max, the entire set of rows is shown. However, when the user enters 15, there are 9 rows with zeroes. So, I am looking to hide those rows where the field is zero. Using that same sheet, if the user then changes the number of months to 18, then that number of rows should be seen. Please help!

 

[kevin9999]

This is certainly doable. Are you looking for a VBA or formula solution? To offer anything of use, we would need to know a lot more about your layout. Are you able to post a copy of your sheet using the XL2BB - Excel Range to BBCode? Or alternatively, share your file via Google Drive, Dropbox or similar file sharing platform.

 

[bbugs73]

I attached a screen shot and included the basic formula I am using. You will see lines 27 - 32, months 19-24 are zero and this I would like to hide. If the user then enters 24, they would be unhidden. As far as solution option, either is fine. I plan on sending this out to others to use so whichever is easier.

 

[kevin9999]

There's a number of different ways you could do this. I'll offer 3 options here.
1. VBA
Right click the sheet tab name, select View Code, and place the following module in the code window that appears on the right of screen. You'll need to save the file as macro-enabled or binary. I've used a data validation rule in cell C7 to only accept whole numbers from 1 to 24.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C7"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Rows("9:32").Hidden = True
        Range("B9").Resize(Target).EntireRow.Hidden = False
    End If
    
Continue:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Result when you enter 15:

hide rows 3 ways.xlsm
	A	B	C	D
1
2
3		Option 1 - VBA
4		Date:
5		Name:
6		Balance:	2000000
7		# of month	15
8
9		1	133333
10		2	133333
11		3	133333
12		4	133333
13		5	133333
14		6	133333
15		7	133333
16		8	133333
17		9	133333
18		10	133333
19		11	133333
20		12	133333
21		13	133333
22		14	133333
23		15	133333
33
Option 1 - VBA

Cell Formulas
Range	Formula
C9:C23	C9	=IF(B9<=$C$7,$C$6/$C$7,0)

Cells with Data Validation
Cell	Allow	Criteria
C7	Whole number	between 1 and 24

2. Conditional Formatting
This doesn't hide the rows, but turns the font white so as to 'disappear'. When you enter 19 in C7 it looks like this:

hide rows 3 ways.xlsm
	A	B	C	D
1
2
3		Date:
4		Name:
5		Balance:	2000000
6		# of month	19
7
8		1	105263
9		2	105263
10		3	105263
11		4	105263
12		5	105263
13		6	105263
14		7	105263
15		8	105263
16		9	105263
17		10	105263
18		11	105263
19		12	105263
20		13	105263
21		14	105263
22		15	105263
23		16	105263
24		17	105263
25		18	105263
26		19	105263
27		20	0
Option 2 - Conditional Format

Cell Formulas
Range	Formula
C8:C27	C8	=IF(B8<=$C$6,$C$5/$C$6,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:C31	Expression	=$B8>$C$6	text	YES

Cells with Data Validation
Cell	Allow	Criteria
C6	Whole number	between 1 and 24

3. Filter function. Your profile says you have 365, so you could use the Filter function like this - only returns the relevant rows but requires a secondary range: When you enter 22 in C7 you get this: (you could place the filtered range on another sheet if you wanted)

hide rows 3 ways.xlsm
	A	B	C	D	E	F	G
1
2
3				Option 3 - Filter formula
4		Date:			Date:
5		Name:			Name:
6		Balance:	2000000		Balance:	2000000
7		# of month	22		# of month	22
8
9		1	90909		1	90909
10		2	90909		2	90909
11		3	90909		3	90909
12		4	90909		4	90909
13		5	90909		5	90909
14		6	90909		6	90909
15		7	90909		7	90909
16		8	90909		8	90909
17		9	90909		9	90909
18		10	90909		10	90909
19		11	90909		11	90909
20		12	90909		12	90909
21		13	90909		13	90909
22		14	90909		14	90909
23		15	90909		15	90909
24		16	90909		16	90909
25		17	90909		17	90909
26		18	90909		18	90909
27		19	90909		19	90909
28		20	90909		20	90909
29		21	90909		21	90909
30		22	90909		22	90909
31		23	0
32		24	0
33
Option 3 - Filter formula

Cell Formulas
Range	Formula
F6:F7	F6	=C6
E9:F30	E9	=FILTER(B9:C32,B9:B32<=F7,"")
C9:C32	C9	=IF(B9<=$C$7,$C$6/$C$7,0)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C7	Whole number	between 1 and 24
F7	Whole number	between 1 and 24

 

[kvsrinivasamurthy]

Try this worksheet event. If the value entered is >24 then C7="" with the message.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$7" Then
Application.EnableEvents = False

If Target <= 24 Then
Rows("9:32").Hidden = False
Rows(9 + Target & ":" & 32).EntireRow.Hidden = True
Else
MsgBox "Value should be <= 24"
Target = ""
Rows("9:32").Hidden = False
End If
Application.EnableEvents = True

End If
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Select worksheet in drop down instead of General
Paste the code
Close the VB window.
Save the file as .xlsm

 

[bbugs73]

There's a number of different ways you could do this. I'll offer 3 options here.
1. VBA
Right click the sheet tab name, select View Code, and place the following module in the code window that appears on the right of screen. You'll need to save the file as macro-enabled or binary. I've used a data validation rule in cell C7 to only accept whole numbers from 1 to 24.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C7"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Rows("9:32").Hidden = True
        Range("B9").Resize(Target).EntireRow.Hidden = False
    End If
   
Continue:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Result when you enter 15:

hide rows 3 ways.xlsm
	A	B	C	D
1
2
3		Option 1 - VBA
4		Date:
5		Name:
6		Balance:	2000000
7		# of month	15
8
9		1	133333
10		2	133333
11		3	133333
12		4	133333
13		5	133333
14		6	133333
15		7	133333
16		8	133333
17		9	133333
18		10	133333
19		11	133333
20		12	133333
21		13	133333
22		14	133333
23		15	133333
33
Option 1 - VBA

Cell Formulas
Range	Formula
C9:C23	C9	=IF(B9<=$C$7,$C$6/$C$7,0)

Cells with Data Validation
Cell	Allow	Criteria
C7	Whole number	between 1 and 24

2. Conditional Formatting
This doesn't hide the rows, but turns the font white so as to 'disappear'. When you enter 19 in C7 it looks like this:

hide rows 3 ways.xlsm
	A	B	C	D
1
2
3		Date:
4		Name:
5		Balance:	2000000
6		# of month	19
7
8		1	105263
9		2	105263
10		3	105263
11		4	105263
12		5	105263
13		6	105263
14		7	105263
15		8	105263
16		9	105263
17		10	105263
18		11	105263
19		12	105263
20		13	105263
21		14	105263
22		15	105263
23		16	105263
24		17	105263
25		18	105263
26		19	105263
27		20	0
Option 2 - Conditional Format

Cell Formulas
Range	Formula
C8:C27	C8	=IF(B8<=$C$6,$C$5/$C$6,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:C31	Expression	=$B8>$C$6	text	YES

Cells with Data Validation
Cell	Allow	Criteria
C6	Whole number	between 1 and 24

3. Filter function. Your profile says you have 365, so you could use the Filter function like this - only returns the relevant rows but requires a secondary range: When you enter 22 in C7 you get this: (you could place the filtered range on another sheet if you wanted)

hide rows 3 ways.xlsm
	A	B	C	D	E	F	G
1
2
3				Option 3 - Filter formula
4		Date:			Date:
5		Name:			Name:
6		Balance:	2000000		Balance:	2000000
7		# of month	22		# of month	22
8
9		1	90909		1	90909
10		2	90909		2	90909
11		3	90909		3	90909
12		4	90909		4	90909
13		5	90909		5	90909
14		6	90909		6	90909
15		7	90909		7	90909
16		8	90909		8	90909
17		9	90909		9	90909
18		10	90909		10	90909
19		11	90909		11	90909
20		12	90909		12	90909
21		13	90909		13	90909
22		14	90909		14	90909
23		15	90909		15	90909
24		16	90909		16	90909
25		17	90909		17	90909
26		18	90909		18	90909
27		19	90909		19	90909
28		20	90909		20	90909
29		21	90909		21	90909
30		22	90909		22	90909
31		23	0
32		24	0
33
Option 3 - Filter formula

Cell Formulas
Range	Formula
F6:F7	F6	=C6
E9:F30	E9	=FILTER(B9:C32,B9:B32<=F7,"")
C9:C32	C9	=IF(B9<=$C$7,$C$6/$C$7,0)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C7	Whole number	between 1 and 24
F7	Whole number	between 1 and 24

The VBA code worked perfectly, thank you so much!

 

[kevin9999]

Happy to help, and welcome to the forum