Help with VBA coding

S

Scandrea

New Member
Joined
Feb 12, 2016
Messages
4
Hi all,

I'm brand new to VBA coding in Excel, and I'm looking to hide/unhide rows based on user responses.

If the user responds '0' in cell G5, I want to hide row 6. If the user responds any number greater than '0', I want to unhide row 6.

Additionally, if the user responds '0' in cell G19, I want to hide rows 20 to 39. If the user responds '1' in cell G19, I want to hide rows 22 to 39. If the user responds '2' in cell G19, I want to hide rows 24 to 39. If the user responds '3' in cell G19, I want to hide rows 26 to 39. If the user responds '4' in cell G19, I want to hide rows 28 to 39. If the user responds '5' in cell G19, I want to hide rows 30 to 39. If the user responds '6' in cell G19, I want to hide rows 32 to 39. If the user responds '7' in cell G19, I want to hide rows 34 to 39. If the user responds '8' in cell G19, I want to hide rows 36 to 39. If the user responds '9' in cell G19, I want to hide rows 38 to 39. And the user responds '10' in cell G19, I want rows 20 to 39 unhidden.

Any help is much appreciated.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Scandrea said:
Hi all,

I'm brand new to VBA coding in Excel, and I'm looking to hide/unhide rows based on user responses.

If the user responds '0' in cell G5, I want to hide row 6. If the user responds any number greater than '0', I want to unhide row 6.

Additionally, if the user responds '0' in cell G19, I want to hide rows 20 to 39. If the user responds '1' in cell G19, I want to hide rows 22 to 39. If the user responds '2' in cell G19, I want to hide rows 24 to 39. If the user responds '3' in cell G19, I want to hide rows 26 to 39. If the user responds '4' in cell G19, I want to hide rows 28 to 39. If the user responds '5' in cell G19, I want to hide rows 30 to 39. If the user responds '6' in cell G19, I want to hide rows 32 to 39. If the user responds '7' in cell G19, I want to hide rows 34 to 39. If the user responds '8' in cell G19, I want to hide rows 36 to 39. If the user responds '9' in cell G19, I want to hide rows 38 to 39. And the user responds '10' in cell G19, I want rows 20 to 39 unhidden.

Any help is much appreciated.
Click to expand...
Hi Scandrea, welcome to the boards.

Try out the following Worksheet_Change macro in a COPY of your workbook. The following code is added directly to the back end of the sheet in question. To do this simply right-click on the tab name at the bottom of the screen and select View Code. In the new window that opens, copy and paste in this code:

Rich (BB code): 
Private Sub Worksheet_Change(ByVal Target As Range)
' Disable screen updating to reduce flicker
Application.ScreenUpdating = False
' G5 rules
    ' If target cell is G5 then select case based on cell value
    If Target.Address = "$G$5" Then
        Select Case Target.Value
            Case 0
                Rows("6").EntireRow.Hidden = True
            Case Is > 0
                Rows("6").EntireRow.Hidden = False
        End Select
    End If
' G19 rules
    ' If target cell is G19 then select case based on cell value

    If Target.Address = "$G$19" Then
        Select Case Target.Value
            Case 0
                Rows("20:39").EntireRow.Hidden = True
            Case 1
                Rows("20:39").EntireRow.Hidden = False
                Rows("22:39").EntireRow.Hidden = True
            Case 2
                Rows("20:39").EntireRow.Hidden = False
                Rows("24:39").EntireRow.Hidden = True
            Case 3
                Rows("20:39").EntireRow.Hidden = False
                Rows("26:39").EntireRow.Hidden = True
            Case 4
                Rows("20:39").EntireRow.Hidden = False
                Rows("28:39").EntireRow.Hidden = True
            Case 5
                Rows("20:39").EntireRow.Hidden = False
                Rows("30:39").EntireRow.Hidden = True
            Case 6
                Rows("20:39").EntireRow.Hidden = False
                Rows("32:39").EntireRow.Hidden = True
            Case 7
                Rows("20:39").EntireRow.Hidden = False
                Rows("34:39").EntireRow.Hidden = True
            Case 8
                Rows("20:39").EntireRow.Hidden = False
                Rows("36:39").EntireRow.Hidden = True
            Case 9
                Rows("20:39").EntireRow.Hidden = False
                Rows("38:39").EntireRow.Hidden = True
            Case 10
                Rows("20:39").EntireRow.Hidden = False
        End Select
    End If
' Re-enable screen updating
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you, Fishboy! It worked perfectly. Doing good deeds for strangers is a virtuous quality. You should feel **** good.

Fishboy said:
Hi Scandrea, welcome to the boards.

Try out the following Worksheet_Change macro in a COPY of your workbook. The following code is added directly to the back end of the sheet in question. To do this simply right-click on the tab name at the bottom of the screen and select View Code. In the new window that opens, copy and paste in this code:

Rich (BB code): 
Private Sub Worksheet_Change(ByVal Target As Range)
' Disable screen updating to reduce flicker
Application.ScreenUpdating = False
' G5 rules
    ' If target cell is G5 then select case based on cell value
    If Target.Address = "$G$5" Then
        Select Case Target.Value
            Case 0
                Rows("6").EntireRow.Hidden = True
            Case Is > 0
                Rows("6").EntireRow.Hidden = False
        End Select
    End If
' G19 rules
    ' If target cell is G19 then select case based on cell value

    If Target.Address = "$G$19" Then
        Select Case Target.Value
            Case 0
                Rows("20:39").EntireRow.Hidden = True
            Case 1
                Rows("20:39").EntireRow.Hidden = False
                Rows("22:39").EntireRow.Hidden = True
            Case 2
                Rows("20:39").EntireRow.Hidden = False
                Rows("24:39").EntireRow.Hidden = True
            Case 3
                Rows("20:39").EntireRow.Hidden = False
                Rows("26:39").EntireRow.Hidden = True
            Case 4
                Rows("20:39").EntireRow.Hidden = False
                Rows("28:39").EntireRow.Hidden = True
            Case 5
                Rows("20:39").EntireRow.Hidden = False
                Rows("30:39").EntireRow.Hidden = True
            Case 6
                Rows("20:39").EntireRow.Hidden = False
                Rows("32:39").EntireRow.Hidden = True
            Case 7
                Rows("20:39").EntireRow.Hidden = False
                Rows("34:39").EntireRow.Hidden = True
            Case 8
                Rows("20:39").EntireRow.Hidden = False
                Rows("36:39").EntireRow.Hidden = True
            Case 9
                Rows("20:39").EntireRow.Hidden = False
                Rows("38:39").EntireRow.Hidden = True
            Case 10
                Rows("20:39").EntireRow.Hidden = False
        End Select
    End If
' Re-enable screen updating
Application.ScreenUpdating = True
End Sub
Click to expand...
 
Upvote 0
Scandrea said:
Thank you, Fishboy! It worked perfectly. Doing good deeds for strangers is a virtuous quality. You should feel **** good.
Click to expand...
Happy to help. Glad to hear it worked as required, and thanks for the positive feedback :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Hide rows based on prior setup sheet
2
Replies
10
Views
217
edjohn20
E
G
  • Solved
Hide and unhide multiple rows based on specific cell values (different cells hide different rows)
Replies
7
Views
1K
Joe4
Joe4
S
  • Question
Hide / unhide columns without using macros / VBA
Replies
1
Views
153
JEC
JEC
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
423
kevin9999
K
M
  • Question
Need multiple buttons to hide/show rows on another tab
Replies
8
Views
390
skillilea
S

Forum statistics

Threads
1,214,825
Messages
6,121,787
Members
449,049
Latest member
greyangel23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top