• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hide & unhide rows as needed

jth

New Member
Hi there,

I would be extremely grateful if someone could help with the below please as I'm fresh out of ideas how to solve this!
Please see the attached.... I want to add a number between 1 and 300 in cell B2. This will then automatically generate the corresponding number of rows, starting at row 5 (rows 1 to 4 inclusive are to be always shown), going down to row 304, but always retaining the "Total Value" row on row 305 (irrespective of how many rows are above).
So for example, if I input "10" in cell B2, I would be able to see down to row 14, rows 15 to 304 would be hidden, and "Total Value" on row 305 would be shown. If I was to input "300" in cell B2, all rows would be shown. I hope that makes sense.

Can anyone help please?
 

Attachments

  • Example.xlsx
    20.6 KB · Views: 4
Hi,​
according to your attachment a beginner starter event demonstration to paste to the Sheet1 worksheet module :​
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim L&
        If Target.Address <> "$B$2" Then Exit Sub
        L = Val(Target):   If L < 1 Then Exit Sub
        Application.EnableEvents = False
    With [B4].CurrentRegion.Rows
        If .Count > 2 Then .Item("3:" & .Count).Clear
    End With
        [C5:D5].ClearContents
        [B5:D5].AutoFill [B5:D5].Resize(L + 1)
        Cells(L + 5, 2).Clear
        Cells(L + 5, 3).Value2 = "TOTAL VALUE"
        Application.EnableEvents = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
  • Like
Reactions: jth
Thanks you very much for the reply, it is most appreciated. I tried to send a condensed version of the spreadsheet but I think in doing so, I have affected how some of the code works. I have therefore attached the full version.
The part of reducing the number of rows to reflect the input cell works fine, but as I have changed the number of rows to be retained at the bottom, this part of the code does not work. Would you mind having a look at the attached to see what needs to be altered in the code please? Apologies for this and once again, many thanks for your help.
 

Attachments

  • Example revA.xlsm
    52.6 KB · Views: 10
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim L&
        If Target.Address <> "$B$3" Then Exit Sub
        L = Val(Target):  If L < 1 Or L > 300 Then Beep: Exit Sub
    With Rows("6:305")
        .Item("1:" & L).Hidden = False
         If L < 300 Then .Item(L + 1 & ":300").Hidden = True
    End With
End Sub
You may Like it !​
 
Further to the above, would anyone be able to help me update the code please?
I need to move the B3 element to cell C9 on another sheet, and the sheet is entitled Main Inputs.
Any help would be hugely appreciated.
Thank you.
 
Sorry to bump my own post... would be great if someone would be able to suggest the necessary amendment to the code please? Please see my post directly above, relating to the reply from 'Marc L' further above. Many thanks.
 
Back
Top