• 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 rows based on condition (Number)

Pasadu

Member
Sir, please i have a data attached where i have the same data, copied and pasted three times on a sheet. The first data is A3:A12, the second is A16:A25, the third is A29:A38.
Now, what i wish for is that, when 1 is inputted in Cell B1, only the first rows should appear (A3:A12) and the other rows for the other data (A16:A38) should be hidden automatically.
When 2 is inputted in cell B1, only the first two data should appear (A3:A25) and the third data (A29:A38) should be hidden automatically.
When 3 is inputted in cell B1, all the three data should be displayed (A3:A38), nothing should be hidden. Thank You.
Microsoft Office Professional Plus 2016 is what I use
81146
 

Attachments

  • AUTOMATED HIDING AND UNHIDING OF ROWS.xlsx
    187.5 KB · Views: 1
This event macro could be a solution; needs to be pasted in the sheet's module:
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sRows  As String
    Dim hRows  As String
    If Target.Cells <> Cells(1, "B") Then Exit Sub
    Select Case Target
        Case 1
            sRows = "3:12"
            hRows = "16:38"
        Case 2
            sRows = "3:25"
            hRows = "29:38"
        Case 3
            sRows = "3:38"
    End Select
    If sRows <> "" Then Rows(sRows).Hidden = False
    If hRows <> "" Then Rows(hRows).Hidden = True
End Sub
 
This event macro could be a solution; needs to be pasted in the sheet's module:
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sRows  As String
    Dim hRows  As String
    If Target.Cells <> Cells(1, "B") Then Exit Sub
    Select Case Target
        Case 1
            sRows = "3:12"
            hRows = "16:38"
        Case 2
            sRows = "3:25"
            hRows = "29:38"
        Case 3
            sRows = "3:38"
    End Select
    If sRows <> "" Then Rows(sRows).Hidden = False
    If hRows <> "" Then Rows(hRows).Hidden = True
End Sub
Thank You Rollis13, it worked perfectly. And i really appreciate it. Thank You sir. But please, do you think there's an excel formula to do same?
 
Glad having been able to help.
Personally I've never seen something like this done with formulas. You can hide contents via Conditional Formatting but not a cell or an entire row or column.
 
Last edited:
Back
Top