CODEWITHSUNDEEP
vbaexcel
jmaz
jmaz

Reputation: 527

Using .Hidden or .SpecialCells(xlCellTypeVisible) to Ignore Hidden Rows -- Not Working

This code uses .Hidden and is intended to ignore hidden rows, but the output fails to do so:

For j = [ESDataRow1].Row To lastEIRPSummaryRow
    If [ESPriPred100].Rows(j).EntireRow.Hidden = False Then
        If [ESPriPred100].Rows(j) >= [ESPRiSpec100].Rows(j) Then
            y2count = y2count + 1
        End If
    End If
Next j

So I tried using .SpecialCells(xlCellTypeVisible), which produces "Application-defined or Object-defined error":

Dim cel As Range, rng As Range
Set rng = Range(Cells([ESDataRow1].Row, 1), Cells(lastEIRPSummaryRow, 1))

For Each cel In rng.SpecialCells(xlCellTypeVisible)
    If [ESPriPred100].Rows(j) >= [ESPRiSpec100].Rows(j) Then
        y2count = y2count + 1
    End If
Next

What is wrong with my code?

Upvotes: 0

Views: 1460

Answers (1)

Jzz
Jzz

Reputation: 739

Sub jzz()
Dim i As Long

For i = 1 To 5
    If Rows(i).EntireRow.Hidden = True Then
        Debug.Print "row: " & i & " is hidden"
    Else
        Debug.Print "row: " & i & " is not hidden"
    End If
Next i
End Sub

Works for me, and I haven't been able to break it.

Can you test with such a simplefied sub? If that works, add 'complexity' step by step and see where it breaks. If it doesn't work, test in a clean (new) workbook.

Upvotes: 2

Related Questions