Excel Macro Challenge

Hi experts,

I'm a little out of my comfort zone here, being an Access developer who now has an Excel VBA challenge.

I have a workbook with many worksheets. On one of the configuration sheets I have a series (perhaps could be a number of named ranges) with yes/no combos. Three columns to the left of each is a unique code no.

As each yes/no is set, it needs to gather the code number, then go to a selected number of other worksheets and find that code in a predetermined colum in each. The worksheets and columns can be stored in a table in another worksheet. Once found (and there will always be a match) the row that the code is in is hidden if the yes/no is no, and unhidden if yes.

To further complicate things, whenever the workbook is opened, it needs to check each and evry one of the yes/no values and ensure that the the appropraite rows are hidden or not.

I'm quite happy to battle through the VBA if I can get a few pointers and equally happy to break this into a number of questions if there's too much here.


LVL 23
Kelvin SparksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin - can you post a sample workbook?
Kelvin SparksAuthor Commented:
I'll make up a sample and upload
Kelvin SparksAuthor Commented:
The sample xlsx file has a number of sheets. If you look at the sheet select product - two rows have yes and one no. Looking at worksheet Admin, it tells us to only use workbooks Wholesale and Retail.

Going back to Select product, there is a yes in the row for product 100, I want it to check the worksheetslisted in Admin and look in the column indicated for that worksheet for the code 100. As this is yes, that row containing the code 100 in each of the Retail and wholesale workbooks should be unhidden. For product code 101, there is a no, so the rows for the 101 code in thosew two sheets are to be hidden. Worksheet Product Details is not listed in Admin and so is to be ignored for this exercise.

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

For worksheet Wholesale I take it should be col B to check rather than C? Does the (un)hiding of rows apply only to those codes in the first sheet - i.e. if other rows are hidden they should be left as they are?
Try this:
Sub x()
Dim rFind As Range, sAddr As String, ws As Worksheet, r As Range, r1 As Range
Dim rProd As Range, rSheet As Range

With Sheets("Select Product")
    Set rProd = .Range("B5", .Range("B" & Rows.Count).End(xlUp))
End With
With Sheets("Admin")
    Set rSheet = .Range("B5", .Range("B" & Rows.Count).End(xlUp))
End With

For Each r In rProd
    For Each r1 In rSheet
        With Sheets(r1.Text).Range(r1.Offset(, 2) & ":" & r1.Offset(, 2))
            Set rFind = .Find(What:=r, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                sAddr = rFind.Address
                    If r.Offset(, 2) = "Yes" Then
                        rFind.EntireRow.Hidden = False
                    ElseIf r.Offset(, 2) = "No" Then
                        rFind.EntireRow.Hidden = True
                    End If
                    Set rFind = .FindNext(rFind)
                 Loop While rFind.Address <> sAddr
            End If
        End With
        sAddr = ""
        Set rFind = Nothing
    Next r1
Next r

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kelvin SparksAuthor Commented:
Thanks, I'm away today, but will try this over the weekend - looking very very promising!

Kelvin SparksAuthor Commented:
Thanks for the very efficient code. I'm having some fun and games with it running against locked or protected sheets, but am working through this.
Kelvin SparksAuthor Commented:

The link below is a followup question for this answer - more points on offer!

Hope you can help

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.