CODEWITHSUNDEEP
excelvba
babsdoc
babsdoc

Reputation: 749

Excel VBA Filtered/Visible Cell

Suppose I have 10 rows of data. I apply some filter to the data and row numbers 7 and 9 are filtered OR visible.

I want to loop through the data (10 Rows) and output the string "Is Visible" in a blank column (suppose Column C), only for the Visible rows (i.e. Range C7 and Range C9).

Upvotes: 14

Views: 58997

Answers (3)

Instant Excel . com
Instant Excel . com

Reputation: 51

Since I came here looking for this a while back, here's something that may be of use to future googlers.

If you're doing it per cell/row,you can just access the cells .EntireRow.Hidden bool directly.

In the below example, it's just a ForEach loop through each Cell in the Selection, and reading the property in that cell only, counting/colouring based on .Hidden being True/False.

Possible pitfalls to beware of..

In the event you're testing a filtered range, you'll need to select beyond the range by a row, as you may not capture the hidden rows if they fall on the penultimate /ultimate row in the range, selecting the first visible row after the selection avoids this.

It's going to be extremely inefficient for large ranges ( 10,000 + rows )

CopyPasta if you want to test



Sub HowManyHiddenCells()

Dim MyCell, MyRange As Range
Dim CellCountAll, CellCountVisible, CellCountHidden As Integer

Set MyRange = Selection



For Each MyCell In MyRange

    ':: IGNORE EMPTY ::
    If Len(MyCell.text) > 0 Then

        If MyCell.EntireRow.Hidden Then
        MyCell.Interior.Color = RGB(255, 220, 200)
        ':: Count of hidden cells in range
        CellCountHidden = CellCountHidden + 1
        ':: Do Column C Text! ::
        MyCell.Offset(0, 2).FormulaR1C1 = "I was hidden! "
        End If
    
        If MyCell.EntireRow.Hidden = False Then
        MyCell.Interior.Color = RGB(200, 255, 180)
        ':: Count of visible cells in range
        CellCountVisible = CellCountVisible + 1
        End If
        ':: Count of all cells in range
        CellCountAll = CellCountAll + 1
    End If
Next MyCell


MsgBox "Cells total " & CellCountAll & vbNewLine & "Hidden :  " & CellCountHidden & vbNewLine & "Visible : " & CellCountVisible, vbOKOnly + vbInformation, "Count of hidden vs visible"

End Sub

Exmaple of script in action - on a filtered range, highlighting the hidden in red

Upvotes: 0

Ralf
Ralf

Reputation: 729

Inspired by @whytheq I came up with this, so it loops through all visible rows in selection:

Sub Loop_through_selected_rows()
Dim rng As Range: Set rng = ActiveWindow.RangeSelection
Dim i As Integer
For i = 0 To rng.Rows.Count - 1
    If Cells(rng.Row + i, 1).EntireRow.Hidden Then
    Else
        Cells(rng.Row + i, 1).Range("A1:E1").Select 'Set Range within row to your needs
        ' Do something here
    End If
Next
End Sub

Upvotes: -1

whytheq
whytheq

Reputation: 35557

Choose some of the first 10 rows to hide, and then try running this

Option Explicit

Sub CheckIfVisible()

Dim i As Integer, x As Integer
x = 0
For i = 1 To 10
    With Excel.ThisWorkbook.ActiveSheet
        If .Rows(i).EntireRow.Hidden Then
        Else
            .Cells(15 + x, 1) = "Row " & i & "is visible"
            x = x + 1
        End If
    End With
Next i

End Sub

Is this the sort of loop you're looking for?
Maybe you can show us your Loop so we can see where your problem is?

Upvotes: 31

Related Questions