Using Named ranges within Variables and hidding rows that contain specific text

[Alexandroid]

I've been trying to get this one working for a few days now but Named Ranges in VBA are being a total pain! I'm sure I'm missing something simple but I am still new to VBA and Excel and just can't figure it out.

I have a sheet with test cases. A test case will consist of a title (first row) and then several steps (the rows below). There are columns of "Pass/Fail" and other columns but they don't matter right now.

I've managed to dynamically create the named ranges for each test case (several test cases per sheet and they don't always have the same amount of steps/rows) just naming them "TestCase1", "TestCase2", etc... in relation to which test case number they are in the sheet.

I want to create a button to automatically hide all the test cases that have Pass in them and only show the test cases that have Failed.

For the sake of the code, an example of the named ranges:

TestCase1 = "=test2!$5:$7"
TestCase2 = "=test2!$9:$12"
TestCase3 = "=test2!$14:$18"

Pass or Fail column is Column "E".

Can anyone help me get this going?

Thanks!

 

[Damon Ostrander]

Hi Alexandroid,

Okay, I am assuming you have succeeded in correctly setting the names of the ranges as in your example, the following code should do what you want. Just create a button and use it to call this macro:

Sub HidePassRanges()
   Dim CaseNo        As Integer
   
   With Worksheets("Test2")
      For CaseNo = 1 To 3
         With .Range("TestCase" & CaseNo)
            If .Range("E1") = "Pass" Then
               .EntireRow.Hidden = True
            Else
               .EntireRow.Hidden = False
            End If
         End With
      Next CaseNo
   End With
   
End Sub

This code is designed to work with only three TestCase names, but I believe it is obvious how to modify it to work with any number of names. I hope you find it helpful.

Damon

 

[Alexandroid]

Thanks Damon! I had the same kind of setup with the "TestCase" & CaseNo but for some reason it wasn't reading it as a named range.

Is there a way that the [If .Range("E1") - "Pass" Then] portion of the code be changed so it looks through all the cells that are in column E but only that are part of the Named range? So for TestCase1, it would only search E5:E7 and TestCase 2 would only search from E9:E12, and so on... It would have to stick within the named range are the size of them changes.

I'm trying to work it into what you gave me but not getting any luck...

Thanks again!

 

[Damon Ostrander]

Hi again Alexandroid,

Yes, I assumed that the Pass/Fail flag was always in column E of the first row of the range. It is quite easy to have the code look in all rows of the range. But I am not sure what what you want to do with the Pass/Fail values found. Are you implying the the Pass/Fail value could be in row 2 of the named range, and all other rows have column E empty? Or are you suggesting there could be conflicting Pass/Fail values? Please clarify and I'll provide the code.

Damon