Autohide row base on numerical value input in a cell

[4inthemorning]

I am not well verse in MSExcel 2007,

I am hoping someone can help me with my problem. I am creating an excel file that will help my work more accurate and more faster. I have posted an image also for reference.

Is is possible to auto hide a row base on numerical value input in a cell?

Example:

I have maximum of 7 years of service, Now if I input the numbers of years needed(e.g. 4 years) the rows exceeding 4 years should be automatically hide (e.g. year 5, year 6 and year 7).

Question.

Is there a function in excel that this situation can be done?

Thank you very much

 

[Peter_SSs]

Welcome to the MrExcel board!

Are they formulas in the bottom table, or just hard-coded values? That is rows 11 to 17?
If so, could you tell what the formulas in row 11 are?

Would a macro solution be acceptable?

 

[4inthemorning]

Yes, sir they are vlookup formulas and some simple mathematical formula. yes sir but the problem is i dont have any experience with macro. but i am very eager to learn. Thank you for welcoming me.

 

[Peter_SSs]

Yes, sir they are vlookup formulas and some simple mathematical formula. yes sir but the problem is i dont have any experience with macro. but i am very eager to learn. Thank you for welcoming me.

Well, you have a couple of options.

A. Formula approach:

You didn't tell me what the formulas in row 11 are so this is a bit of a guess, but never-the-less you could try it.

In
A11 put the formula
=IF(ROWS(A$11:A11)>$C$5,"",your_existing_A11_formula_here)
copy that formula down to A17

in B11:
=IF(ROWS(B$11:B11)>$C$5,"",your_existing_B11_formula_here)
copy down

etc

B. Macro approach.
Leave all your formulas just as they are now and ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try changing the C5 value.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> yrs <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("C5")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>       Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>       yrs = Range("C5").Value<br>       Rows("11:17").Hidden = <SPAN style="color:#00007F">False</SPAN><br>       <SPAN style="color:#00007F">If</SPAN> yrs < 7 <SPAN style="color:#00007F">Then</SPAN><br>        Rows(11).Offset(yrs).Resize(7 - yrs).Hidden = <SPAN style="color:#00007F">True</SPAN><br>       <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>       Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[4inthemorning]

hi sir peter i did what you told me to do and paste the code. but it has an error on the

Rows("11:17").Hidden = False

thank you so much i really appreciate your help sir

 

[Peter_SSs]

it has an error on the

Rows("11:17").Hidden = False

It is working for me.

What is the full error mesage?

What value had you entered in C5 to produce this error?

 

[Peter_SSs]

Actually, I've just looked more closely at you image and can you confirm that B5:C5 are merged?
Mind you, this shouldn't relate to your error message.

 

[4inthemorning]

yes sir they are merge. the error is run time error 1004
unable to set the hidden property of the range class. would you like me to send you the file sir?

thank you so much

 

[Peter_SSs]

yes sir they are merge. the error is run time error 1004
unable to set the hidden property of the range class. would you like me to send you the file sir?

thank you so much

Sounds like your sheet is protected?
Does it have a password?

 

[4inthemorning]

yes sir its protected with a password 123456. i protected it for possible typographical error or i might clear a cell