I need to know How to use Evaluate instead of for each Loop ? as in below simple code:

Sub Loop_Range_2()
 
Dim cel As Range, counter As Long
 
For Each cel In Range("A1:D5")
   counter = counter   1
    cel = counter
Next
 
End Sub

In advance, thanks for all your help.

CodePudding user response：

count = Evaluate("COUNT(A1:D5)")

or, you can use the square-bracket shorthand:

count = [COUNT(A1:D5)]

This article was the one that taught me about the shorthand: https://analystcave.com/excel-vba-evaluate-tips-and-tricks-to-use-application-evaluate/

CodePudding user response：

For Each...Next vs Worksheet.Evaluate vs Array

• Here is a benchmark setup that shows that using an array is in general the most efficient way.
• I only could figure out an Evaluate version by looping through the rows of the range and using ROW function. Note its limitation of 1048576 rows (1024*1024 or 10^20) . The fewer rows, the faster the solution. At the current setup, it is even more efficient than the array version.

Option Explicit

Private Const rCount As Long = 64
Private Const cCount As Long = 16384
Private dT As Double


Sub Loop_Range_2()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
  
    Dim rg As Range: Set rg = ws.Range("A1").Resize(rCount, cCount)
    
dT = Timer
    
    Dim cel As Range, n As Long
     
    For Each cel In rg.Cells
        n = n   1
        cel.Value = n
    Next
 
Debug.Print "For Each...Next Loop = " & Format(Timer - dT, "00.000000") _
    & vbTab & rCount & vbTab & cCount
End Sub


Sub EvaluateRow()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
  
    Dim rg As Range: Set rg = ws.Range("A1").Resize(rCount, cCount)
    
    Dim rrg As Range, c As Long
    
dT = Timer
    
    For Each rrg In rg.Rows
        c = c   1
        rrg.Value = Application.Transpose(ws.Evaluate( _
            "ROW(" & (c - 1) * cCount   1 & ":" & c * cCount & ")"))
    Next rrg

Debug.Print "Evaluate Row         = " & Format(Timer - dT, "00.000000") _
    & vbTab & rCount & vbTab & cCount
End Sub


Sub ArrayLoop()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rg As Range: Set rg = ws.Range("A1").Resize(rCount, cCount)
    Dim Data() As Variant: ReDim Data(1 To rCount, 1 To cCount)
    
    Dim r As Long, c As Long, n As Long
    
Dim dT As Double: dT = Timer
    
    For r = 1 To rCount
        For c = 1 To cCount
            n = n   1
            Data(r, c) = n
        Next c
    Next r
    
    rg.Value = Data
    
Debug.Print "Array Loop           = " & Format(Timer - dT, "00.000000") _
    & vbTab & rCount & vbTab & cCount
End Sub