Excel VBA to hide a certain number of hide rows on one sheet, columns on another, based on number

[gtirrell]

I want to hide a certain number of rows on one sheet, and columns in another, based on the number in a certain cell. I have 30 rows labeled as 1-30 in A11:A40 on Sheet1 and 30 columns on Sheet2 labeled as 1-30 in F9:AI9. When you enter a number in a certain cell (e.g. "7" in cell C8), I want it to hide rows 8-30 on Sheet1 and columns 8-30 on Sheet2.

This works for Sheet1, but I don't know how to reference another sheet or have it simultaneously apply to columns:

Sub Hide_Rows_Columns()

Dim c As Range
For Each c In Range("A11:A40").Cells
If c.Value > Range("C8") Then
c.EntireRow.Hidden = True

End If
Next c

End Sub

Also, can this work just by entering the value in C8 and not having to press run on the macro?

 

[Fluff]

Hi & welcome to MrExcel.
On what sheet are you entering the number into C8?

 

[gtirrell]

Sheet1

 

[Fluff]

Ok how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C8" Then
      Rows("11:40").Hidden = False
      If Target.Value < 30 Then Range("A10").Offset(Target.Value + 1).Resize(30 - Target.Value).EntireRow.Hidden = True
      With Sheets("Sheet2")
         .Range("F9:AI9").EntireColumn.Hidden = False
         If Target.Value < 30 Then .Range("E9").Offset(, Target.Value + 1).Resize(, 30 - Target.Value).EntireColumn.Hidden = True
      End With
   End If
End Sub

This needs to go into the Sheet1 code module.

 

[gtirrell]

I must be missing something pretty basic. I put my original code into the Sheet1 code module and pasted your solution after it. I also tried doing your solution alone, but unfortunately couldn't get anything to happen on Sheet2.

 

[Fluff]

Did you get any errors?
Also did the code work on sheet1?

 

[gtirrell]

I think my original code is still running on Sheet1. If I delete the original code and just run your solution, I just a popup asking for the Macro Name to run, which is all blank.

 

[Fluff]

If you have put the code in the sheet module, then all you need to-do is change C8 & it will run automatically

 

[Fluff]

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: Excel VBA to hide a certain number of hide rows on one sheet, columns on another
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.

 

[gtirrell]

The macro does run automatically and is now working on Sheet2! Thank you!

Sorry I wasn't aware of the rule, thanks for updating me.

Crosspost: Excel VBA to hide a certain number of hide rows on one sheet, columns on another