Macro that hides row if all rows within a given range are empty

[Anonymous321]

Hi, I'm wondering if it's possible to set up a macro that hides a certain row if all rows within a given range are hidden.
For example, if rows 5 to 9 are hidden then hide row 4. I made a code that works on a single row but I want it to work for multiple rows. My problem at the moment is that if row 5 is empty, for example, row 4 will be hidden regardless to whether or not the other rows in the range are not hidden,

This is the code I'm currently using.

If Range("6:10").EntireRow.Hidden Then
Range("5:5").EntireRow.Hidden = True
End If


Any help will be appreciated

 

[darkyxtreme]

Try this, seems stupid but it was working for me.

If Range("6:6").EntireRow.Hidden And Range("7:10").EntireRow.Hidden Then
Range("5:5").EntireRow.Hidden = True
End If

 

[Anonymous321]

Try this, seems stupid but it was working for me.

If Range("6:6").EntireRow.Hidden And Range("7:10").EntireRow.Hidden Then
Range("5:5").EntireRow.Hidden = True
End If

Still running into the same problem sadly . Row 8 Would not be hidden yet it still hides row 5.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Macro that hides row if all rows within a given range are hidden
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Anonymous321]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Macro that hides row if all rows within a given range are hidden
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then the

Sorry, didn't realise there were rules against it

 

[Fluff]

Will A5:A10 always contain values?

 

[Anonymous321]

Will A5:A10 always contain values?

Yes

 

[Fluff]

In that case how about

   If Application.Subtotal(103, Range("A6:A10")) = 0 Then
      Rows(5).Hidden = True
   End If

if rows 6 to 10 are all hidden row 5 will hide.

 

[Anonymous321]

In that case how about

   If Application.Subtotal(103, Range("A6:A10")) = 0 Then
      Rows(5).Hidden = True
   End If

if rows 6 to 10 are all hidden row 5 will hide.

Yes! Thank you, that seems to work. Can I ask quickly what the function of the '103' in in this code, just so I know in the future when a replicate this code? Thanks again, I've been trying to get this to work all day

 

[Fluff]

It tells the subtotal function to count any values ignoring cells that are hidden.