Hide rows based on cell formula output

R

RobbieNZ

New Member
Joined
Aug 17, 2010
Messages
21
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!
 
Sort by date Sort by votes
OMG I took the code out of the worksheet, re-pasted it in again and woop! It is all go.
Why? I have no idea, but I am just so happy!

Chris - you are a legend for putting up with all my queries! Thanks so much :)
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Re: Hide rows based on cell formula output- And hide the Forms Check Boxes as well?

Hi there, Chris patiently sorted out my hide rows macro a couple weeks ago, now I have encountered a problem<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
The Forms Check Boxes I have just added do not hide when a row hides and I am not sure how to counter this? <o:p></o:p>
<o:p></o:p>
I want checkboxes on specific rows to hide when the macro hides the row. Only check boxes on the rows being hidden need to hide (there are other check boxes on the sheet that must stay).<o:p></o:p>
<o:p></o:p>
The checkboxes should automatically hide when a formula in a cell changes, the same as the row hide macro works (see earlier parts of thread)
<o:p></o:p>
And in case it is important-<o:p></o:p>
I have stuck to using Forms Check Boxes rather than Controls Toolbox Check Boxes as the Control Toobox seemed harder to use and the Forms Check box fit my requirements exactly. So if it turns out I need to use check boxes from the Control Toolbox I would need help to get those to work as well sorry.<o:p></o:p>
<o:p></o:p>
Also does anyone have a recommendation on a good beginners VBS book?<o:p></o:p>
Been looking but no idea what to buy.<o:p></o:p>
<o:p></o:p>
Thanks thanks!<o:p></o:p>
Robyn.<o:p></o:p>
 
Upvote 0
ChrisOswald said:
Code: 
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

QUOTE]

Hi, I wonder if you could help. Sorry for hi-jacking your post but i've used this code edited to suit a worksheet that i'm making and it works great.

For example i've used the above to hide rows 20:24 based on a value in cell C7 (repeated for various cases)

Below this routine I'm pasting the same code and changing the variables this time to say something like: Hide row 36 based on the value in cell C6.

The problem is that it stops automatically working, it will work once when I close the VBA window but then if I change the value in C6 it no longer functions.

Can you please advise how I get it to automatically function? Hope i've explained this well enough!

This is what I have:

Private Sub Worksheet_Calculate()
Dim MyResult As String

Application.EnableEvents = False
'first, unhide any rows that are currently hidden.
'This first part will hide the rows associated with the panels depending on number of panels generated by the product code

''''''''''''''''''''''''''''''''''''''''''''''''''
'change the name from Sheet1 to your worksheets name.
''''''''''''''''''''''''''''''''''''''''''''''''''
Rows("1:" & Worksheets("Cutting Sheet for Std Systems").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("Cutting Sheet for Std Systems").Cells(7, 3).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("28:33").EntireRow.Hidden = True
Case "2"
Rows("28:33").EntireRow.Hidden = True
Case "3"
Rows("29:33").EntireRow.Hidden = True
Case "4"
Rows("30:33").EntireRow.Hidden = True
Case "6"
Rows("32:33").EntireRow.Hidden = True
Case "8"
Rows("33:34").EntireRow.Hidden = False


'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
Private Sub Worksheet_Calculate2()
Dim MyResult As String

Application.EnableEvents = False
'first, unhide any rows that are currently hidden.
'This first part will hide the rows associated with the panels depending on number of panels generated by the product code

''''''''''''''''''''''''''''''''''''''''''''''''''
'change the name from Sheet1 to your worksheets name.
''''''''''''''''''''''''''''''''''''''''''''''''''
Rows("1:" & Worksheets("Cutting Sheet for Std Systems").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("Cutting Sheet for Std Systems").Cells(6, 3).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 "XXP", "PXX", "PXXX", "XXXP"
Rows("36").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
Click to expand...
 
Upvote 0
So I have the above working for One Cell, What I now need is to hopefully do it over multiple Cells Ie.

Cell D14, hide rows 15 - 33

Cell D19, hide rows 20 - 21

This using a Yes / No Drop down menu.

So basically if the user selects "No" in cell "D14" then I want "Rows 15 - 33" to be hidden, if the select "Yes" in "D14" but "No" in "D19" then I only want "rows 20-21" hidden and so on.

This May have been answered above will give it a try...
 
Last edited:
Upvote 0
So I Tried to adapt the above and it works once but the second item wont continue working?

Below is my code:


Private Sub Worksheet_Calculate()
Dim MyResult As String

Application.EnableEvents = False

MyResult = Worksheets("Sheet1").Cells(14, 4).Value

Select Case MyResult

Case "No"
Rows("15:33").EntireRow.Hidden = True
Case "Yes"
Rows("15:33").EntireRow.Hidden = False

End Select

Application.EnableEvents = True

End Sub
Private Sub Worksheet_Calculate2()
Dim MyResult As String

Application.EnableEvents = False

MyResult = Worksheets("Sheet1").Cells(19, 4).Value

Select Case MyResult

Case "No"
Rows("20:21").EntireRow.Hidden = True
Case "Yes"
Rows("20:21").EntireRow.Hidden = False

End Select

Application.EnableEvents = True

End Sub
 
Upvote 0
m_parko said:
So I Tried to adapt the above and it works once but the second item wont continue working?

Below is my code:


Private Sub Worksheet_Calculate()
Dim MyResult As String

Application.EnableEvents = False

MyResult = Worksheets("Sheet1").Cells(14, 4).Value

Select Case MyResult

Case "No"
Rows("15:33").EntireRow.Hidden = True
Case "Yes"
Rows("15:33").EntireRow.Hidden = False

End Select

Application.EnableEvents = True

End Sub
Private Sub Worksheet_Calculate2()
Dim MyResult As String

Application.EnableEvents = False

MyResult = Worksheets("Sheet1").Cells(19, 4).Value

Select Case MyResult

Case "No"
Rows("20:21").EntireRow.Hidden = True
Case "Yes"
Rows("20:21").EntireRow.Hidden = False

End Select

Application.EnableEvents = True

End Sub
Click to expand...

Worked out the solution for above:

Private Sub Worksheet_Calculate()
Dim MyResult As String

Application.EnableEvents = False

'begining of case copy, copy from here to the end to enter a new yes no hide

MyResult = Worksheets("Sheet1").Cells(14, 4).Value

'Cell selection above = 14 D

Select Case MyResult

Case "No"
Rows("15:33").EntireRow.Hidden = True

Case "Yes"
Rows("15:33").EntireRow.Hidden = False

End Select

'End of new hide case (End of Copy)

MyResult2 = Worksheets("Sheet1").Cells(19, 4).Value

Select Case MyResult2

Case "No"
Rows("20:21").EntireRow.Hidden = True

Case "Yes"
Rows("20:21").EntireRow.Hidden = False

End Select

Application.EnableEvents = True

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

PuntingJawa
  • Solved
Macro to hide/unhide rows based on cell value
2
Replies
11
Views
287
PuntingJawa
PuntingJawa
M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
477
kevin9999
K
W
  • Question
Automatically hide unpopulated cells/rows for multiple worksheets
Replies
0
Views
627
WillieM01
W
J
  • Question
Insert Row based on cell Value VBA
Replies
2
Views
70
mumps
mumps
G
  • Solved
Stop excel macro from running if there is a blank cell in a column
Replies
5
Views
182
RobP
R

Forum statistics

Threads
1,215,459
Messages
6,124,947
Members
449,198
Latest member
MhammadishaqKhan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top