Hiding rows based on value in cell

Mable

New Member
Joined
Feb 27, 2009
Messages
41
Hi, I wonder if someone could help me please?

I need to hide 8 rows (30-37) based on a value in cell B28.

If the value is 2 then only show rows 30 and 31
If the value is 3 then only show rows 30, 31 and 32
Then continue this up to the user puts in the value of 8 and no rows are hidden.

Thanks for your help with this, it is much appreciated
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Greetings Mable,

In a copy of your workbook:
  1. Right-click the sheet's tab and select 'View Code'
  2. Paste this.
Assuming macros are enabled, this should work.

Hope this helps,

Mark

Code:
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Range("B28"), Target) Is Nothing Then
        
        Rows("30:37").EntireRow.Hidden = False
        
        Select Case Range("B28").Value
            Case 2
                Rows("30:31").EntireRow.Hidden = True
            Case 3
                Rows("30:32").EntireRow.Hidden = True
            Case 4
                ' and so on...
            Case 5
                '...
            Case 6
                '...
            Case 7
            
            Case 8
            
            Case Else
                Rows("30:37").EntireRow.Hidden = False
        End Select
    End If
        
End Sub
 
Upvote 0
You could probably achieve it with an Autofilter solution, but this might do what you want:

Right-click your worksheet tab, click View Code and paste this into the code window.

Press Alt+Q to return to Excel and try changing the value in B28:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim rngToWork, rngToHide As Range
 
If Intersect(Target, Range("B28")) Is Nothing Or Target > 8 Then Exit Sub
 
Application.ScreenUpdating = False
 
Set rngToWork = Range("30:37")
rngToWork.EntireRow.Hidden = False
 
If Target < 1 Then Exit Sub
 
Set rngToHide = rngToWork.Cells(1, 1).Resize(Target, 1)
rngToHide.EntireRow.Hidden = True
 
Application.ScreenUpdating = True
 
End Sub
 
Upvote 0
Application.ScreenUpdating = False

If Target.Row = 28 And Target.Column = 2 Then
Range("a30:A37").Select
Selection.EntireRow.Hidden = False

Dim num_rows As Integer

num_rows = Range("b28").Value - 1


Range("a30").Select

numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + num_rows, numColumns + 0).Select
Selection.EntireRow.Hidden = True
End If
 
Upvote 0
Thanks for all your help peeps! Still having trouble though, i tried all three types of code and they all come back with no errors but when i change the value in the cell nothing happens. All my macros are enabled some not sure whats happening there. You can tell me knowledge of VB is very limited!!
 
Upvote 0
Sorry, messed about with some of my settings and the code worked. Thanks for all your help!
 
Upvote 0
Hi, I wonder if someone could help me please?

I need to hide 8 rows (30-37) based on a value in cell B28.

If the value is 2 then only show rows 30 and 31
If the value is 3 then only show rows 30, 31 and 32
Then continue this up to the user puts in the value of 8 and no rows are hidden.

Thanks for your help with this, it is much appreciated

Hi Mable,

I am assuming that cell B28 is on the same sheet as the rows you want to hide. Is this correct?

Mark
 
Upvote 0
Hi Paste the below code in the workbook code editor window,

Goto vba editor on the left side in the VBA Projects you will see the workbook name under which double click the workbook and paste the below code.... Hope this helps.


Private Sub Workbook_Open()
Sheets("Sheet1").Rows("30:37").Hidden = True

End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Sheets("Sheet1").Range("B28").Value

Case 2
Sheets("Sheet1").Rows("30:37").Hidden = True
Rows("30:31").Hidden = False
Case 3
Sheets("Sheet1").Rows("30:37").Hidden = True
Rows("32:33").Hidden = False
Case 4
Sheets("Sheet1").Rows("30:37").Hidden = True
Rows("34:35").Hidden = False
Case 5
Sheets("Sheet1").Rows("30:37").Hidden = True
Rows("36:37").Hidden = False

End Select
End Sub
 
Upvote 0
Did you follow the instructions:

Right-click your worksheet tab, click View Code and paste this into the code window.

Add this line after the Private Sub line:

Code:
MsgBox "hello"

Now change the value.

edit: you are changing the value in B28 manually, aren't you?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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