Excel VBA - how to check for non-blank values in hidden range

Question

I'll start by making my objective clear, and then explaining it fully.

My goal is to check for non-blank values in a range, but only in the hidden cells of that range, and then use conditional formatting in a different cell, depending on whether the cells in the range are empty or not.

I have a named range called Location_Address_RangeCheck that covers the cells directly to the right of the location numbers, like this (location numbers are not part of the range). When the Number of Locations is changed, the rows that go beyond that number (up to 25) are automatically hidden on worksheet_change to reduce clutter and reduce scrolling to see the stuff below it. That code works fine, so I'm not posting it here so as to not confuse anyone with what I'm trying to accomplish.

I want to provide a safeguard to ensure that there aren't values in the hidden rows that could affect outputs (i.e., if someone selects "3" for Number of Locations, but there is data in cells that might be on the row of the 8th location).

My goal is to check for non-blank values in the range, but only in the hidden cells, and then use conditional formatting in the cell next to the number of locations chosen, depending on whether the cells in the range are empty or not.

So if there is data in the hidden cells, then it would cause the sheet to look like this .

I've tried so many different things so far, but I'm not making any progress. I've scoured the internet trying to find a solution, but everything I've found is about finding things in visible cells, which is the opposite of what I'm trying to achieve.

Here is the code I have written so far, which I know does not achieve my objective:

Sub testhiddencells() 
    Dim myRange As Range 

    Set myRange = Range("Location_Address_RangeCheck") 
    NumRows = Application.WorksheetFunction.CountA(myRange) 

    If Range("Location_Address_RangeCheck").Hidden = True Then 
        If Application.WorksheetFunction.CountA(Range("Location_Address‌_RangeCheck")) <> 0 Then 
            MsgBox "There's something there" 
        End If 
    End If 

End Sub

What part is causing you trouble? .Hidden? Did you look here? — OldUgly
I just can't figure out how to structure it to search only the hidden cells within that range, and to then trigger the conditional formatting in the cell to the right of the number of locations. Or even to just recognize only the hidden cells. I'm doing something wrong, I just can't figure out how to go forward. Here is what I have so far, which I know doesn't work. I'm trying to get it to even recognize only hidden cells. But I'm sleep-deprived and can't figure out what to do. — S.Smith
Sub testhiddencells() Dim myRange As Range Set myRange = Range("Location_Address_RangeCheck") NumRows = Application.WorksheetFunction.CountA(myRange) If Range("Location_Address_RangeCheck").Hidden = True Then If Application.WorksheetFunction.CountA(Range("Location_Address_RangeCheck")) <> 0 Then MsgBox "There's something there" End If End If End Sub — S.Smith

Robin Mackenzie Robin Mackenzie · Accepted Answer · 2016-12-03T02:38:55

Here is a minimal example to check with a cell is both hidden and is non-empty:

Option Explicit

Sub Test()

    Dim ws As Worksheet
    Dim rngToCheck As Range

    'test range - all cells populated with 'a' and 3 are hidden
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rngToCheck = ws.Range("A1:A7")

    If TestForNonBlankCellsInHiddenRange(rngToCheck) Then
        'do you conditional format stuff here
    End If

End Sub

Function TestForNonBlankCellsInHiddenRange(rngToCheck As Range) As Boolean

    Dim rngCell As Range
    Dim blnCheck As Boolean

    'assume that hidden cells are blank
    blnCheck = False

    'iterate range
    For Each rngCell In rngToCheck
        If rngCell.EntireRow.Hidden And Not IsEmpty(rngCell.Value) Then
            'found a hidden and non-empty cell
            blnCheck = True
            'debug address of this cell
            Debug.Print rngCell.Address
        End If
    Next rngCell

    'return check
    TestForNonBlankCellsInHiddenRange = blnCheck

End Function

Looking at the code you used already, you should be able to adapt this to the particular use case of your worksheet.

Excel VBA - how to check for non-blank values in hidden range

1 Answers