Visual Basic Coding - Hide rows.

I have some Visual Basic code I am using in Excel. This section of code has two parts. The first part unhides an entire section of a spreadsheet based on a value in one cell (named TwoA2). That isn't what we need help with. The second part then hides some of those rows if a certain cell in that row is empty. The range of cells that is checked to determine whether the respective row needs to be hidden is TwoATwo. But, that part is hiding all of the rows in the range, including rows where the cell contains either a formula or a simple value. Here is the code, with the relevant part in bold:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("TwoA2").Address Then
   
        Dim cell As Range

   
    ' If x on the line, then hide entire section
        If [TwoA2] = "X" Then
   
            [TwoATwo].Select
            Selection.EntireRow.Hidden = True
            [TwoA2].Select
     
        End If
   
    ' If no x on the line, then unhide instructions and relevant rows
       
        If [TwoA2] <> "X" Then
       
          ' First, unhide entire section
            [TwoATwo].Select
            Selection.EntireRow.Hidden = False
         
          ' Now hide empty rows
            For Each cell In [TwoA2Check]
            If IsEmpty(cell) = True Then
            cell.EntireRow.Hidden = True
            Else
            cell.EntireRow.Hidden = False
            End If
            Next
         
        End If
       
    ' Return cursor to active cell on ending
     
        [TwoA2].Select

       
    End If


   
End Sub
carlosabAsked:
Who is Participating?

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

x
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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
0
Roy CoxGroup Finance ManagerCommented:
Maybe this, it would help to see the actual workbook. In any case you do not need to Select the range.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("TwoA2").Address Then

        ' If x on the line, then hide entire section
        If [TwoA2] = "X" Then
            [TwoATwo].EntireRow.Hidden = True

            ' If no x on the line, then unhide instructions and relevant rows
        ElseIf [TwoA2] <> "X" Then
            ' First, unhide entire section
            [TwoATwo].EntireRow.Hidden = False
            ' Now hide empty rows
            On Error GoTo 0
            [TwoATwo].SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        End If
    End If
End Sub

Open in new window

0
Fabrice LambertFabrice LambertCommented:
Not related to your issue, but to best practices:

The selection object is an evil beast:
- First: it is incredibly slow.
- Second: It impact the graphic interface, wich is unpleasent to watch.
- Third: It is chaotic and unpredictable since it is user dependant (a simple click somewhere from the user chnge it), as a developper you don't want to manipulate chaotic objects.
(Same goes for objects such as activeWorkbook, ActiveCells, ActiveChart, ActiveSheet, the generic Range, Cells, Worksheets collections ect ...)
In other words: Do not use it.
Prefer referencing precisely the objects you intend to use.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

carlosabAuthor Commented:
Roy,

I tried pasting your code in and I couldn't get it to work. I did make some changes that you suggested (stop Selecting range).
 Here is the workbook. Thanks. Book1.xlsm
0
Roy CoxGroup Finance ManagerCommented:
I'll take a look later for you
0
Roy CoxGroup Finance ManagerCommented:
Is this what you are trying to do?

Note I have removed all the merged cells in the range to hide. MergedCells are generally problematic and should be avoided

I have highlighted the cell to trigger the code.

Select X to hide all the rows, U to unhide all the rows, and H to hide empty rows
Book1.xlsm
0
carlosabAuthor Commented:
Yes, except that the cell you highlighted (D7 or TwoA2) needs to be a formula because the user will not manually enter the value there. When I replaced D7 with: =A1, and then type the values into A1, nothing happens.
0
Roy CoxGroup Finance ManagerCommented:
I don't see the point in your formula. Surely my suggestion is more efficient.
0
carlosabAuthor Commented:
The worksheet that I uploaded is vastly simplified. The workbook where I will be using the code needs to have the rows automatically hidden (or unhidden) depending on certain values. Those values are calculated automatically based on other data that is input by the user. So, I need to be able to run the code at issue on a value that is calculated from a formula, rather than manually input. Thanks again.
0
Roy CoxGroup Finance ManagerCommented:
Then the code that I have provided needs changing to work with the results of the formula. Change the select case criteria to match the expected formula results.

  Select Case [TwoA2]
    Case "X"
        [TwoA2Check].EntireRow.Hidden = True
        Case "U"
        [TwoA2Check].EntireRow.Hidden = False
    Case Else: [TwoA2Check].EntireRow.Hidden = False
        ''             Now hide empty rows
        For Each rCL In [TwoA2Check]
            If Len(rCL) = 0 Then rCL.EntireRow.Hidden = True
        Next rCL

    End Select

Open in new window

0

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
carlosabAuthor Commented:
That's what I did. I replaced D7 with this: =A1. Then I typed the values into A1. That causes the exact value to be reproduced in D7 as the result of the formula in D7. When I typed a U in A1, that same value is populated automatically into D7, which I expect to then unhide the rows. But it doesn't.
0
Roy CoxGroup Finance ManagerCommented:
The actual cell that is changing is A1 therefore the code isn't being run.
0
carlosabAuthor Commented:
That makes sense. Is there a function that will evaluate the results of a formula (i.e. =A1) and then use the result of that formula as the information to evaluate?
0
carlosabAuthor Commented:
Thanks for the help on this. I was able to get it to work.
0
Roy CoxGroup Finance ManagerCommented:
Glad you got it working. If you attach  an example I'll look at the formula for you
0
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
Visual Basic Classic

From novice to tech pro — start learning today.