Search code examples
excelvbavlookup

VBA Hide Rows in Sheet Based on the Vlookup Value in a Named Range


I have a VBA Sub that is supposed to lookup values from a Named Range and if it finds a match, hide a certain number of rows. But it's not working. In fact, it's producing results that I don't understand how it reached those results.

Sub subHideSundays()
    varMonthsArray = Range("ranMonths") 'LISTS!C11:C23
    For Each varMonth In Range("ranMonths")
        Debug.Print varMonth
        For lngRowNumber = 7 To 534 Step 17
            Debug.Print varMonth.Range("B" & lngRowNumber).Text
            If varMonth.Range("B" & lngRowNumber).Text Like "Sun*" Then
                varMonth.Rows(lngRowNumber - 1).Resize(17).Hidden = True
            Else
                varMonth.Rows(lngRowNumber - 1).Resize(17).Hidden = False
            End If
        Next
    Next
End Sub

The line "Debug.Print vbaMonth" does exactly what I expect it to do. Displays the name of the month (sheet) that contains the value which is being searched for.

But the line "Debug.Print varMonth.Range("B" & lngRowNumber).Text" which is supposed to show the Vlookup value from the month sheet (i.e., cell "JANUARY!B7") that is being searched for in the Named Range (varMonth), is actually returning the value of the cell referenced by ("B" & lngRowNumber), of the "LISTS" sheet. Which is not even included in the Named Range.

Any help would be greatly appreciated, as this is causing a stumbling block in getting the program ready for testing.

Thanks to all that reply.


Solution

  • There is no line in the code as Debug.Print vbaMonth. Do you mean Debug.Print varMonth? Anyway: you explanation is quite vague. If i have to guess you are looking for something like

    Debug.Print Sheets(varMonth.Value2).Range("B" & lngRowNumber).Text
    

    Therefore your code might be something like this:

    Sub subHideSundays()
        
        Dim RngMonth As Range
        Dim LngRowNumber As Long
        Dim WksMonth As Worksheet
        
        For Each RngMonth In Range("ranMonths")
            
            Debug.Print RngMonth
            
            For LngRowNumber = 7 To 534 Step 17
                            
                Set WksMonth = Sheets(RngMonth.Value2)
                
                With WksMonth
                    
                    Debug.Print .Range("B" & LngRowNumber).Text
                    
                    If .Range("B" & LngRowNumber).Text Like "Sun*" Then
                        
                        .Rows(LngRowNumber - 1).Resize(17).Hidden = True
                        
                    Else
                        
                        .Rows(LngRowNumber - 1).Resize(17).Hidden = False
                        
                    End If
                    
                End With
                
            Next
            
        Next
        
    End Sub
    

    In your original code your varMonth is set in the For Each Next cycle as the first (and second and third and so on) cell of the named range ranMonths. Therefore when you use varMonth you are actually referring to the cell, the actual cell containing the information you were seeking and not the information itself. You are basically telling VBA to take that cell as a starting point for your research. By setting a worksheet variable as the actual sheet using the value of the given cell, you can instruct VBA to pick the right sheet for your search.