Arrays

Hi,

I am struggling to speed up a process, and I wondered whether a multi-dimensional array would help (but I am having difficulty in getting it to work correctly).

Please see the attached example - what I want to do is to evaluate whether the cell value is <>0 and if it is to write the following information into an array:

Column a value; column B value; row 2 value; amount

This should give me a file of 24 entries

[my actual file will be 40,000 + entries]

I did write a process that went through and did the above, but it does take a  long time (450,000 cells evaluated, producing 40,000 entries - took well over 20 minutes - even with application.screenupdating = false etc).

I thought that writing the values to a file, and then out again would be quicker, but I confess that I need some assistance to achieve this. By the way, I do know how many entries there will be because the early part of the process uses COUNTIF(range,"<>0") to evaluate how many cells will need to be written out.

Any help / suggestions / guidance will be much appreciated.

Hi Alan

Why do't you just read all of your data straight into an array in one task (0 values as well) and then do all your processing on the array itself.

When you have finished processing, write the array back out to the same location (or elsewhere) as one single hit.

Sub mdarray2()
    Dim strRange As String
    Dim int_col As Integer
    Dim lng_row As Long
    Dim val_array() As Variant
    Dim lng_val As Long
    Dim lngrwindex As Long
    Dim lngcolindex As Long
    Dim lnggo As Long
    Dim lngcounter As Long
    Worksheets("Sheet1").Range("A1").Select
  
    Call Goto_Last(strRange, int_col, lng_row)
   
    Range("A1").FormulaR1C1 = "=COUNTIF(R[2]C[2]:R[" & lng_row - 1 & "]C,""<>0"")"
    lng_val = Range("A1").Value
    MsgBox lng_val
    
      ' now read in all of your data - you don't need to redim the array
    val_array = Range(Cells(2, 1), Cells(lng_row, int_col))
    
    ' now do whatever processing you want on the 13 x 7 array
    ' row 1 is the header row
    
    
    ' at the end of processing, write the data out ( I have written it to sheet 2 in this example)
    ' write the data out again
    Sheets("Sheet2").Range("A2").Resize(lng_row - 1, int_col) = val_array
    
    End Sub

Alan

I think that you have got confused about the dimensions of your output array. As far as I can see, this should be a two dimensional array with lng_val rows and 4 columns.

In the code below, I put the spreadsheet data into a variant array varData and do the data manipulation on the array as this is much quicker than looping through cells on the spreadsheet.

It took about 3 s on my ancient machine to process 90000 entries.

Sub mdarray2()
    Dim val_array() As Variant
    Dim lng_val As Long
    Dim varData As Variant
    Dim lC As Long
    Dim lR As Long
    Dim lngcounter As Long
       
    'quick and dirty VBA to find the total number of non-zero values
    lng_val = Application.WorksheetFunction.CountIf(Range("C3").Resize(Range("C" & Cells.Rows.Count).End(xlUp).Row - 2, 5), "<>0")
    ReDim val_array(1 To lng_val, 1 To 4)

   'put the spreadsheet data into an array for faster data handling
    varData = Range("A1").Resize(Range("A" & Cells.Rows.Count).End(xlUp).Row, 7)
    lngcounter = 1
   
    For lR = 1 To UBound(varData, 1)
        For lC = 3 To UBound(varData, 2)
            If Val(varData(lR, lC)) <> 0 Then
                val_array(lngcounter, 1) = varData(lR, 1)
                val_array(lngcounter, 2) = varData(lR, 2)
                val_array(lngcounter, 3) = varData(2, lC)
                val_array(lngcounter, 4) = varData(lR, lC)
                lngcounter = lngcounter + 1
            End If
        Next lC
    Next lR
   
End Sub

Peter

Thank you both for your very quick replies - they were extremely helpful.

So much so, that evaluating 450,912 data cells and writing into and then out of the array took just 2 seconds!!