• 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.

Hiding or showing rows

PMC

New Member
Hello all,

I am slowly getting into the world of VBA and Macros to which I have a question.

On the attached file there are a number of rows hidden. I would like it to function so that if I enter 3 into F2, the rows row 8 would be shown. If the value in F2 changes to 5 then rows 11 to 55 would be hidden etc.
I have seen some similar questions to this on this already on this site and others, but they usually focus on hiding rows where a particular value is found.

Many thanks in advance for your input.
 

Attachments

  • Hidden Rows.xlsm
    12.9 KB · Views: 2
Hi,​
according to your attachment a beginner starter event you must paste to the Sheet2 worksheet module :​
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Const R = 6
    Dim L&
    If Target.Address = "$F$2" Then
        If Target.Value2 < 1 Or Not IsNumeric(Target.Value2) Then Beep: Exit Sub
        Rows(R).Resize(Target.Value2).Hidden = False
        L = Me.UsedRange.Rows(Me.UsedRange.Rows.Count).Row
        If Target.Value2 <= L - R Then Rows(Target.Value2 + R & ":" & L).Hidden = True
    End If
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Another way not needing to hide any row just managing directly the column A numbering :​
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
        Const R = 6
    If Target.Address = "$F$2" Then
        If Target.Value2 < 1 Or Not IsNumeric(Target.Value2) Then Beep: Exit Sub
        Range(Cells(R, 1), Cells(R - 1, 1).End(xlDown)).ClearContents
        Cells(R, 1).Resize(Target.Value2).Value2 = Evaluate("ROW(1:" & Target.Value2 & ")")
    End If
End Sub
You may Like it !​
 
Hi thanks,

there was another comment asking what should happen if F2 is different than 3 or 5.

The number of displayed rows should reflect the value in F2.
Value of 1 in F2, then 1 displayed row,
Value of 2 in F2, then 2 displayed rows,
Value of 3 in F2, then 3 displayed rows, etc.

This should continue up to a maximum of 50.
 
Back
Top