hide rows based on value in a cell on another sheet?

[aedctalk]

Hello. I could really use some help on how to do this...

I have a value on Configuration!J12 It is a dropdown ranging in numbers from 1 to 30

On sheet Call! Rows 6 - 40
are the rows i want to hide based on number selected on configuration!J12

If configuration!J12 is 1 i want to hide all rows from 7-40
if its 2 i want to hide rows 8-40
if its 12 i want to hide rows 18- 40

etc.

when the number selected is changed from 3 to say 25 then back to 4.. i need rows to be unhidden/hidden based upon number

How could i most simply do this?

Thank you sooo much for any help

 

[VoG]

See if you can adapt this http://www.mrexcel.com/forum/showthread.php?t=548627

 

[Marcelo Branco]

Hi,

Assuming your two sheets are named Configuration and Call, maybe this

Copy the macro below to a standard module (Alt+F11, Insert, Module and paste in the right-panel)

Sub hideRows()
    With Sheets("Call")
        .Range("A7", "A40").EntireRow.Hidden = False
        .Range("A" & Sheets("Configuration").Range("J12").Value + 6, "A40").EntireRow.Hidden = True
    End With
End Sub

and this one to the page-code of the sheet Configuration (right-click on the sheet-tab, View Code and paste in the right-panel)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("J12")) Is Nothing Then
        Call hideRows
    End If
End Sub

HTH

M.

 

[aedctalk]

Thankyou so much!!

Marcello! your's works great!! There is one issue!


I see that it is adding + 6 ..

My values are from A6:A40

It works perfect for any number under 34..

As soon as i select 35 (only 34 rows show up) and it hides row 41

As soon as i select 40 (my highest number... only 34 show up) and it hides rows 41 to 47...

How could i got about fixing this issue using marcello's code above? please :P

Thank SO SO MUCH!

 

[Marcelo Branco]

Hello. I could really use some help on how to do this...

I have a value on Configuration!J12 It is a dropdown ranging in numbers from 1 to 30

Hi,

I thought your values could not be above 30, but in your last post you are talking about 35, 36 till 40...

I'm not sure what you need. Please, clarify.

M.

 

[aedctalk]

Hello Thanks for your continued help marcelo (sorry i was using innaccurate cell #'s and sorry.. i meant up to 35

My rows i'm working with hiding/showing are Call!L6:L40

My configuration!J49 is where i have dropdown Dropdown has values to show 10 rows, 15, rows, 20 rows, 25 rows, 30 rows, 35 rows

When i select 35.. it hides rows L40 & L41

Here's your modied to fit my cell numbers code that i'm using

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("J49")) Is Nothing Then
        Call hideRows
    End If
End Sub
 
Sub hideRows()
    With Sheets("Call")
        .Range("A6", "A40").EntireRow.Hidden = False
        .Range("A" & Sheets("Configuration").Range("J49").Value + 6, "A40").EntireRow.Hidden = True
    End With
End Sub

Here is a image of what i'm working with below

 

[Marcelo Branco]

If i understtod correctly, the possible values in Configuration!J49 are
10, 15, 20, 25, 30 and 35
and if you select 10 you want to show 10 rows and so on.

But, let me ask: which 10 rows, 15 rows,...... 35 rows?

For example, for 10, do you want to show A6:A15 and for 15 do you want A6:A20.

If i'm correct, try this

Sub hideRows()
    With Sheets("Call")
        .Range("A6", "A40").EntireRow.Hidden = True
        .Range("A6", "A" & 5 + Sheets("Configuration").Range("J49").Value).EntireRow.Hidden = False
    End With
End Sub

HTH

M.

 

[aedctalk]

Thank you ... got ir right on the money!

Really appreciate the help! I now totally understand how it works too! (Well for rows anyway).. lets hope i dont need t hide columns anytime soon lol

Thanks again

 

[aedctalk]

Actually... if you or someone else wouldnt mind helping I think i do need to use the same formula in order to hide COLUMNS too!

Configuration!J38 will be number of box stations

The sheet they will be working on is Friday!

I attached a image below so you can get a visual

On Friday! My column for box stations starts at F & Ends At M


If the person says they have 2 box stations in configuration!j38.. I would like for the script to show only column F & G and hide all other columns through M

Is there any way at all to do this?

Or to easily alter this code in order for it to work?

   With Sheets("Friday")
        .Range("A119", "A138").EntireRow.Hidden = True
        .Range("A119", "A" & 118 + Sheets("Configuration").Range("J44").Value).EntireRow.Hidden = False
    End With

Thank you thank you thankk you!

 

[Marcelo Branco]

Hi,

To hide columns, try this

Sub hideColumns()
    With Sheets("Friday")
        .Columns("F:M").EntireColumn.Hidden = True
        .Range("F:F").Resize(, Sheets("Configuration").Range("J38")).EntireColumn.Hidden = False
    End With
End Sub

And dont forget to change the Worksheet_Change event in the Configuration sheet to

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("J49")) Is Nothing Then
        Call hideRows
    End If
    
    If Not Intersect(Target, Range("J38")) Is Nothing Then
        Call hideColumns
    End If
End Sub

Hope i have got the job done

M.