Enumming by row and col the fast way?

  • Thread starter Thread starter Edwin Knoppert
  • Start date Start date
E

Edwin Knoppert

I want to use For Each to enumerate a sheet's contents rapidly.
However, i need to access that by Row/Col combination.

For Each obtains all data before the for /next loop procedes and is
therefore the quickest.

Simply obtaining each cell's data without for each is useless.

Any ideas?

The following pseudocode shows what i need but does not work since it's not
a collection and is therefore slower:

Object Get oWorkSheet.UsedRange.Cells To oCells
For Row = 1 to oWorkSheet.UsedRange.Rows.Count
For Col = 1 to oWorkSheet.UsedRange.Columns.Count
Print oCells( Row, Col )
Next Col
Next Row

Someone mentioned to obtain the variantdata using:
variantarray = oExcel.Range( vRange ) .Value
But the data is now sequential and i don't think i can rely on it's order i
think.
This order might easily change in the next Excel version??
 
Edwin,

You are loading the range values into an array, which you can step through
the same way, essentially:

Dim VariantArray As Variant
Dim i As Integer
Dim j As Integer
VariantArray = oExcel.Range(vRange).Value
For i = LBound(VariantArray, 1) To UBound(VariantArray, 1)
For j = LBound(VariantArray, 2) To UBound(VariantArray, 2)
Print VariantArray(i, j)
Next j
Next i

HTH,
Bernie
MS Excel MVP
 
Back
Top