Speeding up a search on hidden rows

[RawlinsCross]

Good day,

I have a sub routine that scans down through a 2-dimensional range and checks for hidden rows. If the row is not hidden, it puts that row in an array. Nothing wrong with the code, works great - although it slows down at very large rows.count. I've done speed tests where I remove the 'For Loop' and it's clearly that section of code that is causing the issue. Is there a way to replace that 'For Loop' and still get the same effect (i.e. non-hidden rows in an array)?

Dim wSht As Worksheet
Dim rRange As Range
Dim vArray As Variant
Dim i As Long, j As Long, iCount As Long

Set wSht = ThisWorkbook.Worksheets("WOs")
Set rRange = wSht.Range("rngWOs")

ReDim vArray(1 To rRange.Rows.Count, 1 To 20)

For i = 1 To rRange.Rows.Count
    If rRange.Rows(i).Hidden = False Then
        iCount = iCount + 1
        For j = 1 To rRange.Columns.Count
            vArray(iCount, j) = rRange.Cells(i, j)
        Next j
    End If
Next i

'Redimension
vArray = Application.Transpose(vArray)
ReDim Preserve vArray(1 To 20, 1 To iCount)
vArray = Application.Transpose(vArray)

'Call another procedure that does something with the vArray

End Sub

 

[Peter_SSs]

You're welcome. Glad you got something useful out of the conversation.

if you try to vArray special cells (xlVisible), it would only include those rows up to the first hidden row.

Strictly speaking, that is not quite correct. It consists of the first visible area. So, if the first 1 or more rows are hidden it does return rows after the first hidden row.

Here is another option that is ball-park twice as fast again.*

Sub VisibleToArray_1()
  Dim wSht As Worksheet
  Dim rRange As Range, rVis As Range, c As Range
  Dim vArray As Variant, aRws As Variant
  Dim i As Long
 
  Set wSht = ThisWorkbook.Worksheets("WOs")
  Set rRange = wSht.Range("rngWOs")
  Set rVis = rRange.Columns(1).SpecialCells(xlVisible)
  ReDim aRws(1 To rVis.Count)
  For Each c In rVis
    i = i + 1
    aRws(i) = c.Row
  Next c
  With Application
    vArray = .Index(wSht.Cells, .Transpose(aRws), .Transpose(Evaluate("row(1:20) + " & rRange.Column - 1)))
  End With
  'Call another procedure that does something with the vArray
End Sub

With new sample data (random numbers) of 20,000 rows x 20 columns with about 12,000 rows visible in about 5,000 separate areas, my timings were as follows.
* Of course the actual and relative times would depend on the size of the data and how many rows are visible and in how many disjoint areas as well as machine capability etc.

Post #: Time in seconds
1 : 0.938
2 : 0.313
3 : 0.102
5 : 0.133
11: 0.051