CODEWITHSUNDEEP
vbaexcel
Kev
Kev

Reputation: 3

excel hide rows with error values based on dropdown change

I would like to hide rows in column B9-B40 when Index/Match formula returns "N/A" , now I managed to get it done in VBA when the table is static but I have a dynamic table based on drop-down selection which means the number of N/As returned can be different for each time drop-down selection changes.

Here is what I have right now which hides the rows with N/A based on current drop-down selection, my dropdowns are in C2,C3 and C4. But it doesn't takes any further change in the dropdown into consideration afterwards. I am not very competent with the VBA so any help would be great.

Thanks. 

Option Explicit


Sub hide_if_error()

    Dim MyCell As Range, Rng As Range

    Set Rng = Range("B9:B40")

    For Each MyCell In Rng

        If IsError(MyCell) Then

            MyCell.EntireRow.Hidden = True

        End If

    Next MyCell

End Sub

Upvotes: 0

Views: 536

Answers (1)

QHarr
QHarr

Reputation: 84465

You can do 

Range("B9:B40").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True

You will need a test in advance that errors are present though.

That might look like:

Option Explicit
Public Sub hide_if_error()
    With ThisWorkbook.Worksheets("Sheet4")

        .Range("B9:B40").EntireRow.Hidden = False

        If Evaluate("=SUM(IF(ISERROR(" & .Range("B9:B40").Address & "),1))") > 0 Then '<==check if any errors present

            .Range("B9:B40").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True

        End If
    End With
End Sub

If necessary, you could link the above to a worksheet change or dropdown change event so it is fired each time there is an update using the dropdown(s)

For example:

If you data validation was in C2:C4 you would put in the code pane for the sheet the following event code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHand
    If Not Intersect(Target, Range("C2:C4")) Is Nothing Then
        Application.EnableEvents = False
        hide_if_error
    End If
ErrHand:
Application.EnableEvents = True
End Sub

Note:

The worksheet event code goes in the code pane associate with sheet 4:

Code pane

The other code goes in a standard module (module 1 here):

Module 1

Example run:

Test run

Upvotes: 1

Related Questions