CODEWITHSUNDEEP
vbaexcel
Tawm
Tawm

Reputation: 545

VBA VisibleCell is not Visible

I've got an issue where a CellIsInVisibleRange function selects a cell which it shouldn't.

The code that's looping is as follows:

wCount = 0
    loopCount = 0
    For Each wCell In wRange
        loopCount = loopCount + 1
        If CellIsInVisibleRange(wCell) Then
            MsgBox ("HERE IT BE")
            wCount = wCount + 1
            "codey-code"
        End If
    Next

The function which fails for me is:

Function CellIsInVisibleRange(cell As Range)

     CellIsInVisibleRange = Not Intersect(ActiveWindow.VisibleRange, cell) Is Nothing

End Function

What's happening is, I've got a visible range of rows being 1, 64, 65, 66. This loop accepts a supposedly visible wCell in Row 45.

I'm looking at this sheet, and that row is most definitely not visible.

Do you see what's wrong with this function?

Thanks.

EDIT:

@KekuSemau

Thanks for the reply. That code works well but I wonder if there is a more efficient way to go about doing this. The line wCell.SpecialCells(xlCellTypeVisible) returns a "Type mismatch" error but I feel like something like this would be quicker. I suppose I should have shown that I declare the range I'm seeking over as well.

Set wRange = Range("$" & nameColumnLetter & "$1:$" & nameColumnLetter & "$" & lastRow)
    wCount = 0
    loopCount = 0
    For Each wCell In wRange
        loopCount = loopCount + 1
        'If CellIsInVisibleRange(wCell) Then
        If wCell.SpecialCells(xlCellTypeVisible) Then
            code
            code
            code
        End If
    Next

Am I close to a quicker method here?

Thanks again.

Upvotes: 1

Views: 875

Answers (3)

user4691433
user4691433

Reputation:

My work-in-progress answer until we get to discuss a little, here's how I would revise your code:

Set wRange = Range("$" & nameColumnLetter & "$1:$" & nameColumnLetter & "$" & lastRow)
    wCount = 0
    'counts blanks and non-blanks, changed from original based on my guess of what you want
    loopCount = wRange.Count 
    Set wRange2 = wRange.SpecialCells(xlCellTypeVisible)
    For Each wCell In wRange2
        code
        code
        code
    Next wCell

Upvotes: 2

TnTinMn
TnTinMn

Reputation: 11801

The Range.EntireRow and Range.EntireColumn range objects have a property named "Hidden" that can be checked prior to performing a computationally expensive Intersect evaluation. In this case, you would check if cell.EntireRow.Hidden is hidden. If it is hidden, there is no point checking if it is within the visible range.

Public Function CellIsInVisibleRange2(cell As Range) As Boolean
    If (cell.Worksheet Is ActiveWindow.VisibleRange.Worksheet) Then
        If Not cell.EntireRow.Hidden Then
            CellIsInVisibleRange2 = Not (Application.Intersect(cell, ActiveWindow.VisibleRange) Is Nothing)
        End If
    End If
End Function

Upvotes: 0

KekuSemau
KekuSemau

Reputation: 6856

ActiveWindow.VisibleRange does not seem to be very 'intelligent', it bluntly returns the Range form the topleft cell to the bottomright cell.
Try it out: Go to a new sheet, hide Rows 5-10, then hide column E, for example.
Then type this in the immediate window in the VB Editor: 

?ActiveWindow.VisibleRange.Address

It will output something like $A$1:$M$47, ignoring missing ranges.

You can intersect the VisibleRange with the non-hidden-cells like this: 

Public Function CellIsInVisibleRange(cell As Range) As Boolean
    CellIsInVisibleRange = False
    ' wrong sheet active?
    If Not cell.Worksheet Is ActiveWindow.VisibleRange.Worksheet Then Exit Function

    ' <Really> visible range
    Dim rng As Range
    Set rng = Intersect( _
        ActiveWindow.VisibleRange, _
        ActiveWindow.VisibleRange.Worksheet.Cells.SpecialCells(xlCellTypeVisible))

    CellIsInVisibleRange = Not Intersect(rng, cell) Is Nothing
End Function

Upvotes: 2

Related Questions