In VBA, does a check for a value slow down or speed up calculation?

[largeselection]

I have a workbook where there are various groups of cells where users enter values. I have code which translates that worksheet into a nice clean data table. Essentially the code loops through the defined ranges and checks every cell to see if there is a value > 0. If there is a positive value, then it adds it to the data table on the next tab and lists the row # and col # where that value is found. Then it moves onto the next one until it has checked all the cells in the defined range. The reason why I ask if the "If" check slows things down is because it is taking a long time to complete the process. There are approx 18,000 cells in the defined range, in my test there were approx 11,000 that actually had a value > 0. So it took about 10 minutes to complete the process. My original thinking is that it would be faster since it is checking 18,000 cells that it would be able to immediately skip 7,000 since they are blank. But now I'm thinking it might not be faster since rather than just doing the process of listing the value and the row/column associated for every cell it has to stop to do that check first and maybe that's slowing it down.

Any thoughts on if this is helpful or not? Also, if anyone has a better suggestion on how to get this data into a data table that would be helpful too. Example of sheets and code is below:

Data on sheet 1 looks like:

	A	B	C	D
1	23	23	42	19
2	11	15		24

<tbody>
</tbody>

Desired Output on sheet 2 looks like:

Row	Col	Value
1	1	23
1	2	23
1	3	42
1	4	19
2	1	11
2	2	15
2	4	24

<tbody>
</tbody>

 Set tWS = ActiveSheet Set iRange = Range("Data.Grid")


 Sheets("DATA").Range("A3:BZ75000").ClearContents
 
 lRow = Sheets("DATA").Range("A" & Rows.Count).End(xlUp).Row + 1
 
 For Each iCell In iRange.Cells
     If Len(iCell.Value) > 0 Then
         iCol = iCell.Column
         iRow = iCell.Row
         With Sheets("DATA")
             .Cells(lRow, 1).Value = iRow
             .Cells(lRow, 2).Value = iCol
             .Cells(lRow, 3).Value = iCell.Value
         End With
     lRow = lRow + 1
     End If
 Next

 

[JackDanIce]

This suggestion, based on your code above, should run faster:

Sub Macro1()

    Dim x       As Long
    Dim y       As Long
    
    Dim var     As Variant
    Dim arr()   As Variant
    Dim dic     As Object
    
    arr = Range("Data.Grid").value
    Set dic = CreateObject("Scripting.Dictionary")
         
    Application.ScreenUpdating = False
         
    For x = LBound(arr, 1) To UBound(arr, 1)
        For y = LBound(arr, 2) To UBound(arr, 2)
            If LenB(arr(x, y)) > 0 Then dic(x & "|" & y) = arr(x, y)
        Next y
    Next x
                                
    x = 2
    With Sheets("DATA")
        .Cells.ClearContents
        For Each var In dic
            .Cells(x, 1).value = Split(CStr(var), "|")(0)
            .Cells(x, 2).value = Split(CStr(var), "|")(1)
            .Cells(x, 3).value = dic(var)
            x = x + 1
        Next var
    End With
            
    Application.ScreenUpdating = True
            
    Erase arr
    Set dic = Nothing

End Sub

 

[largeselection]

Awesome thank you for the suggestion! I never thought about creating a dictionary. It works well, though I can't test it speed-wise fully. I should have mentioned that my defined range is actually a collection of ranges (ie- I have a named range called Data.Grid = Input!$AT$36:$CB$141,Input!$AT$162:$CB$467). And the bit of code that you included stops after the first continuous range in the named range rather than going through all of them. Additionally (and I know this is completely my fault because of the example I outlined), the range is not actually beginning at cell A1, the first range begins at AT36 so yours lists row 1, col 1 of the range which is actually row 36, column 46. Is it possible to adjust it to include all the ranges in the named range and list the actual row/column address? I'm not that familiar with scripting dictionaries...

Thank you so much for the suggestion though!

 

[JackDanIce]

Glad to help.

A lot of the time, slow code is a result of repeatedly writing to and from the spreadsheet. If you can load the data into an array and process in memory, then return the results in a single 'action' it's usually a faster/more efficient way. Using a dictionary here, simply because I'm being lazy in defining another output array to return the results for Data, easier to work with a Dictionary for me here.

Having said that, without knowing what data ranges you have that aren't consecutive, not easy to create an 'all-in-one' solution.

What you might like to try is create a procedure that takes in a single range as it's argument and processes output to the Data sheet. Then you can repeatedly call this procedure with different range arguments to append into the results in Data.

 

[largeselection]

Great suggestion!

I'll try that and see how it works out!

 

[JackDanIce]

Good luck!