Hide rows based on cell formula output

[RobbieNZ]

Hi there, I am a total novice with VB and spent all yesterday reading through forums and help sites. (I promise to go buy a helpbook for dummies this weekend so I can skill up) But for my job this week

I want to set up an automatic macro within a sheet called 'Q U O T E'
that will evaluate cell "A91" in which the formula will either return a
blank cell (with the formula still sitting in it), 'None' or one of five product codes.
If the formula returns a 'blank' or 'None' result I want to hide rows 92:110

The idea is to hide a section of the 'Q U O T E' sheet if the component is not selected by a customer.
Ideally I would like to be able to apply this same automatic process to other sections of the same sheet.

All help appreciated
Thanks!

 

[ChrisOswald]

You'll have to decide how you want this snippet to fire, plus build in the other cases, but it should put you on the right path

Sub RowHiderSnippet()
    Dim MyResult As String
 
    MyResult = Cells(91, 1).value
    Select Case MyResult
    Case "", "None"
        Rows("92:110").EntireRow.Hidden = True
    Case "2"
        Rows("2:4").EntireRow.Hidden = True
    End Select
End Sub

 

[RobbieNZ]

Hi,
I tried the above (Thanks heaps), but do not know how to fire it automatically when the formula in A91 changes.

At the moment it just spits the dummy saying I have a compile error "Invalid outside procedure" at MyResult = Cells(91, 1).Value (It highlights the 91) But this could just be because I don't know how to make it run??

Can anyone spoon feed me a fix as I obviously have no idea what I am doing (

 

[ChrisOswald]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyResult       As String
    'first, unhide any rows that are currently hidden.
 
     Rows("1:" & ActiveSheet.UsedRange.Rows.Count).EntireRow.Hidden = False
    'Cells(91,1) mean the cell in the 91 st row and the 1 st column, ie A91
    'MyResult is the variable that takes it value.
    MyResult = Cells(91, 1).Value
 
    'This is the spot where you'd add your other cases.
    'Just keep adding your cases as in the examples.  Be sure that all the cases are
    'in between the Select     and the End Select statements
 
    Select Case MyResult
 
    'Including the value 0 in case of a direct a = b type of thing; if you're confident that
    '0 will never mean Empty String, remove it from the case
    Case "", "None", "0"
        Rows("92:110").EntireRow.Hidden = True
    Case "2"
        Rows("2:4").EntireRow.Hidden = True
    Case "142"
        Rows("22:45").EntireRow.Hidden = True
    Case "xyz"
        Rows("1:3").EntireRow.Hidden = True
        'Case Else is a special case that happens if none of the above values are
        'detected in cell A91.  It is optional.
    Case Else
        Rows("91:91").EntireRow.Hidden = True
    End Select
 
    'If you have any rows that should be always hidden, you can
    ' "rehide" any rows that you want always hidden.
    Rows("5:6").EntireRow.Hidden = True
End Sub

To install this code, rightclick on the tab of the sheet you want to run it from to get to the Visual Basic Editor. Copy and paste, being sure to include the Sub and End sub lines. The lines that start with an apostrophe (they'll probably look green to you) are comments - the code will skip over them.

The values of Case will be your part numbers. You'll need to change the bits in the Rows("a:b").entirerow.hidden = true to fit your requirements.

 

[RobbieNZ]

AWESOME!!
Thanks so much, looking forward to learning how to do this myself.

Best,
R.

 

[RobbieNZ]

Hey Chris, hmmm, thought it was all go go gaget, but only working if I manually adjust a91, rather than if the formula in that cell, which is linked to a different sheet, changes.

Can the macro automatically run from a formula change rather than an actual manual type in change?
At the moment I have cell A91 refrencing ='CUSTOMER ENQUIRY'!B4 which changes to the values we are using for CASE

Does the macro, instead need to be linked to the CUSTOMER ENQUIRY SHEET B4 where a drop down box is used to select the data for CASE?

Thanks thanks )

R.

 

[ChrisOswald]

Sorry, used wrong event logic. The only events I use on a regular basis are Workbook events, and only rarely do I use a worksheet event, so you'll have to pardon the confusion.

Two possible solutions. Replace the earlier code with this in the worksheet module.

Private Sub Worksheet_Calculate()
    Dim MyResult       As String
 
    Application.EnableEvents = False
    'first, unhide any rows that are currently hidden.
 
    ''''''''''''''''''''''''''''''''''''''''''''''''''
    'change the name from Sheet1 to your worksheets name.
    ''''''''''''''''''''''''''''''''''''''''''''''''''
    Rows("1:" & Worksheets("Sheet1").UsedRange.Rows.Count).EntireRow.Hidden = False
    'Cells(91,1) mean the cell in the 91 st row and the 1 st column, ie A91
    'MyResult is the variable that takes it value.
 
 
 
    ''''''''''''''''''''''''''''''''''''''''''''''''''
    'change the name from Sheet1 to your worksheets name.
    MyResult = Worksheets("Sheet1").Cells(91, 1).Value
    ''''''''''''''''''''''''''''''''''''''''''''''''''
 
 
    'This is the spot where you'd add your other cases.
    'Just keep adding your cases as in the examples.  Be sure that all the cases are
    'in between the Select     and the End Select statements
    Select Case MyResult
        'Including the value 0 in case of a direct a = b type of thing; if you're confident that
        '0 will never mean Empty String, remove it from the case
    Case "", "None", "0"
        Rows("92:110").EntireRow.Hidden = True
    Case "2"
        Rows("2:4").EntireRow.Hidden = True
    Case "142"
        Rows("22:45").EntireRow.Hidden = True
    Case "xyz"
        Rows("1:3").EntireRow.Hidden = True
        'Case Else is a special case that happens if none of the above values are
        'detected in cell A91.  It is optional.
    Case Else
        Rows("91:91").EntireRow.Hidden = True
    End Select
 
    'If you have any rows that should be always hidden, you can
    ' "rehide" any rows that you want always hidden.
    Rows("5:6").EntireRow.Hidden = True
    Application.EnableEvents = True
End Sub

You'll want to change the Sheet1 to the name of the sheet you want this to work on. I added the Application.enableeevents bit to get rid of flicker. I'm not sure what'll happen if you run this on a massively recalculated spreadsheet.

Or, you could use the original on the worksheet where the value changes. You'll have to replace the activesheet / worksheets("SheetWhatever") to reflect the sheet you're messing with.

I suggest that you use the method where you have the best chance of finding the code 12 months from now.

 

[RobbieNZ]

Hi there, I put the below in to the 'Q U O T E' worksheet VB code area and tried to get it to run. Based on the instructions, I changed the sheet name and deleted the other cases for now so I don't confuse myself. BUT it is playing dead duck and I can't get it to do anything.

ALso one further question, once this is actually going, can I make my other cases
Case "xyz"
Rows("92:108").EntireRow.Hidden = False
so if the rows are already hidden they will reappear? Or am I hoping for too much ?

Thanks again!!

WHAT I ACTUALLY COPIED IN
Private Sub Worksheet_Calculate()
Dim MyResult As String

Application.EnableEvents = False

Rows("1:" & Worksheets("Q U O TE").UsedRange.Rows.Count).EntireRow.Hidden = False


MyResult = Worksheets("Q U O T E").Cells(91, 1).Value
Select Case MyResult

Case "", "None", "0"
Rows("92:108").EntireRow.Hidden = True

Case Else
Rows("91:91").EntireRow.Hidden = True
End Select

Application.EnableEvents = True
End Sub

 

[ChrisOswald]

By dead duck, do you mean you get a "subscript out of range" error? The code you reposted has a typo in it. You're missing a space between the T and E of Q U O T E.

Rows("1:" & Worksheets("Q U O TE").UsedRange.Rows.Count).EntireRow.Hidden = False

I'm not entirely certain what you mean by

ALso one further question, once this is actually going, can I make my other cases
Case "xyz"
Rows("92:108").EntireRow.Hidden = False
so if the rows are already hidden they will reappear? Or am I hoping for too much ?

The first line of code in the post is there to unhide everything on that sheet.

 

[RobbieNZ]

Hi Chris,
Sorry I meant that it does nothing. Before we made it worksheet speific it would fire if I manually changed data in the cell. Now I do not get any errors (that typo is not in the script) but it doesn't do anything at all. Oh ok I didn't think bout the first unhide, my second query is redundant then.

Thanks much,
Robyn.

Code:
Private Sub Worksheet_Calculate()
Dim MyResult As String

Application.EnableEvents = False
'first, unhide any rows that are currently hidden.

''''''''''''''''''''''''''''''''''''''''''''''''''
'change the name from Sheet1 to your worksheets name.
''''''''''''''''''''''''''''''''''''''''''''''''''
Rows("1:" & Worksheets("Q U O T E").UsedRange.Rows.Count).EntireRow.Hidden = False
'Cells(91,1) mean the cell in the 91 st row and the 1 st column, ie A91
'MyResult is the variable that takes it value.



''''''''''''''''''''''''''''''''''''''''''''''''''
'change the name from Sheet1 to your worksheets name.
MyResult = Worksheets("Q U O T E").Cells(91, 1).Value
''''''''''''''''''''''''''''''''''''''''''''''''''


'This is the spot where you'd add your other cases.
'Just keep adding your cases as in the examples. Be sure that all the cases are
'in between the Select and the End Select statements
Select Case MyResult
'Including the value 0 in case of a direct a = b type of thing; if you're confident that
'0 will never mean Empty String, remove it from the case
Case "", "None", "0"
Rows("92:108").EntireRow.Hidden = True

'Case Else is a special case that happens if none of the above values are
'detected in cell A91. It is optional.
Case Else
Rows("91:91").EntireRow.Hidden = True
End Select

'If you have any rows that should be always hidden, you can
' "rehide" any rows that you want always hidden.
Application.EnableEvents = True
End Sub