Excel Hide Rows If Macro Code

June 9, 2013 at 23:37:28
Specs: Windows XP
I have a massive excel spreadsheet containing 6 worksheets and need to hide certain rows throughout the workbook if certain cells in 'sheet1' are left blank. I also need excel to automatically hide the rows b/c other people will be using the workbook. I don't want them to have to hit "run macro" each time they modify the cells in 'sheet1'. Also, ALL the rows in worksheets 2 thru 6 contain formulas if that matters. Sheet1 does NOT contain any formulas.

If 'Sheet1' B22 is left blank, then I need excel to hide the following rows:

'Sheet2' row 11
'Sheet4' row 10, 26,27,28,57,68,78,90,107,108,109
'Sheet5' row 7
'Sheet6' row 6,7,8,9,50,51,52,53

If 'Sheet1' B23 is left blank, then I need excel to hide the following rows:

'Sheet2' row 12
'Sheet4' row 11, 29,30,31,58,69,79,91,110,111,112
'Sheet5' row 7
'Sheet6' row 6,7,8,9,50,51,52,53


I'm trying to make slight adjustments to the following macro to accomadate my worksheet but it's not working at all:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyCells As Range

'Which cells do you want to monitor for changes?
Set MyCells = Range("A1:A42")

'Check to see if the changed cell is one we care about
If Intersect(Target, MyCells) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub 'Don't do anything is multiple cell changes

'Turn off system settings
Application.EnableEvents = False
Application.ScreenUpdating = False

Worksheets("Sheet1").Rows(22 + Target.Row).Hidden = Target.Value = ""

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


See More: Excel Hide Rows If Macro Code

Report •

June 10, 2013 at 13:42:25
First, a posting tip for this forum:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum.

As for your question, what is this line supposed to be doing?

Worksheets("Sheet1").Rows(22 + Target.Row).Hidden = Target.Value = ""

That is not an instruction that VBA will recognize and I don't see how it relates to your requirements.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

June 11, 2013 at 06:37:23
No, it's valid. Target.Value = "" is a boolean test, and the results of said test are saved in the boolean Range.Hidden. This is typically more explicit in other languages (they use == for equality checks), however you run the risk of assigning a value inside of a if statement when you meant to test against it.

How To Ask Questions The Smart Way

Report •
Related Solutions

Ask Question