Auto hide rows based on cell value

Question

Hi all,

I'm very sorry if this has been answered elsewhere, but I'd like to hide a select number of rows based on a cell value and perform this several times within a sheet.

Ie.:

Based on the cell value in C3, I'd potentially like rows 5 to 23 hidden.

Based on the cell value in C25, I'd potentially like rows 26 to 44 hidden.

Based on the cell value in C48, I'd potentially like rows 56 to 65 hidden.

And so on, there's about another 10 hidden sections the same as the 3rd line above.

I've seen some answers that are similar but without understanding the intricacies of coding I've failed in making the changes work for me.

Any help will be HUGELY appreciated!

Answer 1

Hi Andrew

You did not specify the criteria you will use to hide rows in each cases,

so change portions of text in BOLD according your needs

Note:

1-I choose to show you on each case different type of criteria to evaluate.

Change them accordingly

2- You must repeat the IF conditions portions of code as many sections you want to evaluate

3- Change the ranges and row numbers accordingly

This is the code for the Worksheet Private event

*******************************************************************************************

Private Sub Worksheet_Change(ByVal Target As Range)

''this line will Unhide all rows in the sheet

 Rows("1:" & Rows.Count).EntireRow.Hidden = False

''''Your conditions as per criteria

 If Range("C3") > 50 Then

 Rows("5:23").EntireRow.Hidden = True

 End If

 If Range("C25") = "Your criteria" Then

 Rows("26:44").EntireRow.Hidden = True

 End If

 If Range("C48") <= 100 Then

 Rows("56:65").EntireRow.Hidden = True

 End If

End Sub

*****************************************************************************

Check picture below

Do let me know if you require any further help on this. Will be glad to help you.

If this answer would be the solution to your question, Please, share your appreciation by marking it as answered. I would be grateful to you as well.

Thanks

Regards

Jeovany CV

Answer 2

Hi Jeovany,

Many many thanks for your help. Sorry for not including the data. I've tried your solution with my own amendments with no success.

C3 is a drop down cell and the data I'm basing actions on is:

If C3="Yes - 4801 Certified", then rows 5-23 are hidden

If C3="No", then rows 5-23 are hidden

If C3="Yes", then rows 5-23 are not hidden

If C25="Yes - 14001 Certified", then rows 26-44 are hidden

If C3="No", then rows 26-44 are hidden

If C3="Yes", then rows 5-23 are not hidden

I'm also getting a privacy error when trying to save? This is what I entered based on my understanding of what you sent earlier:

Thank you very much for your help with this.

Answer 3

Please confirm if the second set of if statements is correct

If C25="Yes - 14001 Certified", then rows 26-44 are hidden

If C3="No", then rows 26-44 are hidden

If C3="Yes", then rows 5-23 are not hidden                                                                                                     

Is this statement belong to a second dropdown list in cell C25?

Are they link to cell C3?

Do let me know so I could finish the macro and give you the correct answer

Answer 4

Hi Jeovany, I'm sorry that's my bad and thanks for picking it up.

Correct requirements are:

If C25="Yes - 14001 Certified", then rows 26-44 are hidden

If C25="No", then rows 26-44 are hidden

If C25="Yes", then rows 26-44 are not hidden

Thanks for your patience and help with this. I'm going to apply your advice often so the learning sticks!

Answer 5

Hi Andrew

Please find in the below code the answer to your question

**********************************************************************

Private Sub Worksheet_Change(ByVal Target As Range)

''this line will Unhide all rows in the sheet

 Rows("1:" & Rows.Count).EntireRow.Hidden = False

''''Your conditions as per criteria for cell C3

 If Range("C3") = "Yes - 4801 Certified" Or Range("C3") = "No" Then

 Rows("5:23").EntireRow.Hidden = True

 Else

 Rows("5:23").EntireRow.Hidden = False

 End If

'''Your conditions as per criteria for Cell C25

 If Range("C25") = "Yes - 14001 Certified" Or Range("C25") = "No" Then

 Rows("26:44").EntireRow.Hidden = True

 Else

 Rows("26:44").EntireRow.Hidden = False

 End If

End Sub

************************************************************************

Regarding the error I'm not sure if  2 Worksheet_Change(ByVal Target As Range)

events can be at the same time (same module) in a worksheet.

I think is better create a module place the two macros in it and then call them from the procedure

**********************************************************************************

Do let me know if you require any further help on this. Will be glad to help you.

If this answer would be the solution to your question, Please, share your appreciation by marking it as answered. I would be grateful to you as well.

Thanks

Regards

Jeovany CV