Find Longest String in an Array Excluding Hidden Rows

[ttbuson]

I have the following code which should find the longest string in an array. I want to modify it to exclude hidden rows, however. Any ideas on how I might do this?

Dim cell As Range
Dim strLongestCellRef As String
Dim intLongestCellLen As Integer
For Each cell In Sheets("Sheet1").Range("B6:B45")
    If Len(cell) >= intLongestCellLen Then
        intLongestCellLen = Len(cell)
        strLongestCellRef = cell.Value
    End If
Next cell

 

[sergioMabres]

Hi ttbuson,
I see a couple of ways to do this here is two

Sub findlongest1()
  Dim cell, rng As Range
   Dim strLongestCellRef As String
   Dim intLongestCellLen As Integer
   Set rng = Sheets("Sheet1").Range("B6:B45").SpecialCells(xlCellTypeVisible)
   For Each cell In rng
      If Len(cell) >= intLongestCellLen Then
          intLongestCellLen = Len(cell)
          strLongestCellRef = cell.Value
      End If
  Next cell
End Sub

Sub findlongest2()
    Dim cell As Range
    Dim strLongestCellRef As String
    Dim intLongestCellLen As Integer
    For Each cell In Sheets("Sheet1").Range("B6:B45")
        If cell.Height > 0 Then
            If Len(cell) >= intLongestCellLen Then
                intLongestCellLen = Len(cell)
                strLongestCellRef = cell.Value
            End If
        End If
    Next cell
End Sub

Using ".SpecialCells(xlCellTypeVisible)" is more elegant and is specialty tailored for filtered rows
Using "If cell.Height > 0 Then" is simpler to understand and works but it works on filtered and hidden rows
Cheers
Sergio