Excel Hide Rows When Value Entered

[padilAgrotani]

I have to develop an excelsheet where a row is hidden automatically when the value is enter. There is 3 condition
An example like this:
Condition 1
If cell C16 = Value 1 to 3
Row 17 -> automatically hide row
Row 18 -> automatically hide row
Row 19 -> automatically unhide row

Condition 2
If cell C16 = Value 4 to 6
Row 17 -> automatically hide row
Row 18 -> automatically unhide row
Row 19 -> automatically hide row

Condition 3
If cell C16 = Value 7 to 9
Row 17 -> automatically unhide row
Row 18 -> automatically hide row
Row 19 -> automatically hide row
etc.
Can you please help me?
Thanks a lot

 

[Hui]

PadilAqrotani

Firstly, Welcome to the chandoo.org Forums

Copy this code into the Worksheet Code Module for the worksheet you want it to apply to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address <> "$C$16" Then Exit Sub

Select Case Target
Case 1 To 3
  Rows("17:19").EntireRow.Hidden = False
  Rows("17:18").EntireRow.Hidden = True

Case 4 To 6
  Rows("17:19").EntireRow.Hidden = False
  Rows("17").EntireRow.Hidden = True
  Rows("19").EntireRow.Hidden = True

Case 7 To 9
  Rows("17:19").EntireRow.Hidden = False
  Rows("18:19").EntireRow.Hidden = True

End Select

End Sub

Enter values in C16 and see what happens

You will have to save the file as a Macro Enabled file *.xlsm

 

[Hui]

PadilAqrotani

It has also been noted that this post is Cross Posted

Please read the site rules at: http://forum.chandoo.org/link-forums/new-users-please-read.17/
Especially the section on Cross Posting.

 

[padilAgrotani]

thanks a lot...its really helpfull

 

[padilAgrotani]

need some help again..for C21, C26 and so on...how to combine it.

 

[padilAgrotani]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address <> "$C$16" Then Exit Sub

Select Case Target
Case 1 To 3
Rows("17:19").EntireRow.Hidden = False
Rows("17:18").EntireRow.Hidden = True

Case 4 To 6
Rows("17:19").EntireRow.Hidden = False
Rows("17").EntireRow.Hidden = True
Rows("19").EntireRow.Hidden = True

Case 7 To 9
Rows("17:19").EntireRow.Hidden = False
Rows("18:19").EntireRow.Hidden = True

If Target.Address <> "$C$21" Then Exit Sub

Select Case Target
Case 1 To 3
Rows("22:24").EntireRow.Hidden = False
Rows("22:23").EntireRow.Hidden = True

Case 4 To 6
Rows("22:24").EntireRow.Hidden = False
Rows("22").EntireRow.Hidden = True
Rows("24").EntireRow.Hidden = True

Case 7 To 9
Rows("22:24").EntireRow.Hidden = False
Rows("23:24").EntireRow.Hidden = True

End Select

End Sub


I have try several times..C21 not working.

 

[Hui]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$C$16" Then 

Select Case Target
Case 1 To 3
Rows("17:19").EntireRow.Hidden = False
Rows("17:18").EntireRow.Hidden = True

Case 4 To 6
Rows("17:19").EntireRow.Hidden = False
Rows("17").EntireRow.Hidden = True
Rows("19").EntireRow.Hidden = True

Case 7 To 9
Rows("17:19").EntireRow.Hidden = False
Rows("18:19").EntireRow.Hidden = True

else If Target.Address = "$C$21" Then 

Select Case Target
Case 1 To 3
Rows("22:24").EntireRow.Hidden = False
Rows("22:23").EntireRow.Hidden = True

Case 4 To 6
Rows("22:24").EntireRow.Hidden = False
Rows("22").EntireRow.Hidden = True
Rows("24").EntireRow.Hidden = True

Case 7 To 9
Rows("22:24").EntireRow.Hidden = False
Rows("23:24").EntireRow.Hidden = True

End Select

end if

End Sub

 

[padilAgrotani]

I got this error..

https://pasteboard.co/GE1zK1J.jpg

 

[NARAYANK991]

Hi ,

Change that line of code to the following :

ElseIf Target.Address = "$C$21" Then

Narayan

 

[padilAgrotani]

Compile Error:

Else Without If

 

[padilAgrotani]

This are sample

Need to do it for C16, C21, C26, C31, C36, C41, C46 and C51

 

[NARAYANK991]

Hi ,

Replace the entire code with this :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            If Target.Address = "$C$16" Then
              Select Case Target
                      Case 1 To 3
                          Rows("17:19").EntireRow.Hidden = False
                          Rows("17:18").EntireRow.Hidden = True

                      Case 4 To 6
                          Rows("17:19").EntireRow.Hidden = False
                          Rows("17").EntireRow.Hidden = True
                          Rows("19").EntireRow.Hidden = True

                      Case 7 To 9
                          Rows("17:19").EntireRow.Hidden = False
                          Rows("18:19").EntireRow.Hidden = True
              End Select

            ElseIf Target.Address = "$C$21" Then
                  Select Case Target
                          Case 1 To 3
                              Rows("22:24").EntireRow.Hidden = False
                              Rows("22:23").EntireRow.Hidden = True

                          Case 4 To 6
                              Rows("22:24").EntireRow.Hidden = False
                              Rows("22").EntireRow.Hidden = True
                              Rows("24").EntireRow.Hidden = True

                          Case 7 To 9
                              Rows("22:24").EntireRow.Hidden = False
                              Rows("23:24").EntireRow.Hidden = True
                  End Select
            End If
End Sub

Narayan

 

[NARAYANK991]

Hi ,

See whether this is correct.

Narayan

 

[NARAYANK991]

Hi ,

Note that I have gone with the Worksheet_SelectionChange event procedure , though the correct event to be used should be the Worksheet_Change event procedure.

In case you want the change to be made , let me know.

As it stands , you need to take the cursor to each of the mentioned cells for the rows to be hidden / unhidden. If you are in a cell such as C16 , and you change the value from one slab to another (for example if it was 2 and you changed it to 5) , then you have to move the cursor out of C16 and then bring it back to C16 for the rows to be hidden / unhidden.

If the Worksheet_Change event procedure were to be used , this would not be so ; the moment the cell value is changed , the rows will be hidden / unhidden.

Narayan

 

[padilAgrotani]

Another problem...help pls

For C51

Case 1 To 4
Row 52 -> automatically hide row
Row 53 -> automatically unhide row

Case 5 To 9
Row 52 -> automatically unhide row
Row 53 -> automatically hide row

 

[NARAYANK991]

Hi ,

See if this is correct.

Narayan

 

[padilAgrotani]

Hi ,

See if this is correct.

Narayan

Working perfectly, thanks a lot.

 

[Hui]

Thanx Narayan

Padil, If you look at the structure of the code you should be able to work out how to extend it