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?
 
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
 
Ryan ChongCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.