hide/show rows when cells in Column A are blank - based on data validation drop down list in a cell

[Yodelayheewho]

Hello,

I have searched the threads and cannot find the solution that fits my situation.

I have a worksheet with a data validation drop down menu in E3. This list is comprised of four types of services: Service 1, Service 2, Service 3, Service 4.

For each service, there is a unique set of tools, products and auxiliary items needed to complete the service.

Service 1 has 3 rows of tools, 3 rows of products and no auxiliary items
Service 2 has 3 rows of tools, 3 rows of products and no auxiliary items
Service 3 has 8 rows of tools, 5 rows of products and 9 rows of auxiliary items
Service 4 has 8 rows of tools, 5 rows of products and 9 rows of auxiliary items

I understand I can't accomplish what I need with a formula. So, I need to create a simple VBA (I'm a novice) that automatically hides/shows rows based on the drop down selection made in E3.

I apologize if I've not posted this correctly.

I appreciate any assistance you would provide.

 

[jorismoerings]

Hi,

Able to share a mockup of the way it should look?

 

[Yodelayheewho]

Sorry for my delayed response.

The attached screen shots show Service 1 and Service 3. You really only need to focus on Column A since I removed proprietary information in the other columns. Notice that for Service 1 I had to put a note in the Auxiliary Section to let users know there are no Auxiliary items for Service 1, but Service 3 there is, which you can see in the second screen shot. What I would prefer is to have all rows hide if the cell in Column A is blank.

 

[Yodelayheewho]

Obviously, I'm new to this. Here's the link

https://www.dropbox.com/sh/f5zy07g8k69l9fr/AAArqcthOGUHYQtN30hlK1Apa?dl=0

 

[jorismoerings]

If you shared the actual mockup in excel it would have been easier but that's for next time..

try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rSelect As Range

Set rSelect = Intersect(Target, Range("E3"))
If Not rSelect Is Nothing Then
    Application.EnableEvents = False
        Select Case rSelect '1 line hide all rows in the section, 2nd line shows necesarry lines
            Case Is = "Service 1", "Service 2"
                'Next 2 lines show or hide tooling section
                Rows("6:13").EntireRow.Hidden = True
                Rows("6:8").EntireRow.Hidden = False
                'Next 2 lines show or hide product section
                Rows("18:22").EntireRow.Hidden = True
                Rows("18:20").EntireRow.Hidden = False
                'Next 2 lines show or hide aux section
                Rows("24:32").EntireRow.Hidden = True
            Case Is = "Service 3", "Service 4"
                'Next line show tooling section
                Rows("6:13").EntireRow.Hidden = False
                'Next line show product section
                Rows("18:22").EntireRow.Hidden = False
                'Next line show aux section
                Rows("24:32").EntireRow.Hidden = False
        End Select
    
End If

This VBA code needs to be copied onto the code page of the worksheet so not in a separate module.

 

[Yodelayheewho]

Hi jorismoerings!

I can't seem to get this to work. So, I uploaded the spreadsheet here: https://www.dropbox.com/s/2uq1kb81ujkrexf/SERVICE Cost Estimator_SAMPLE.xlsx?dl=0

I really appreciate your assistance.

 

[jorismoerings]

Check this