Use AutoFilter or Loop through the file in VBA Office 2013

Question

So I am trying to achieve a task where I would fetch values from an excel file. Make the necessary modifications to the values, and then add them to the Powerpoint Presentation.

Hence, to achieve the goal. I used the developer tools in Power point and started writing a vba script. Now the Goal of getting values from another excel file and adding them to powerpoint, was more simple and straightforward. HOWEVER, when I am trying to fetch some Specific values from the excel file.

For Example, the Values, who have value of "5" in Column C (which will only have number 1-5).

And I was wondering/Confused as to what approach would be more faster/efficient in this case.

Option A) Use Autofilter, to filter the list, and then loop through the now visible Cells? Option B) Go through all the columns, and then fetch the values?

I was wondering if the AutoFilter Excel Provides has some sort of advantage over normal For Each Loop in Vba? And Hence would be faster?

NOTE: The VBA Script will be ran from powerpoint, So To Use Excel Workbook and commands, I need to create an object of "Excel.Application"

Edit: Correction of naming.

Answer 1

If it comes to speed in Excel, then you need to think of the follwing things:

• Every read/write to a cell comes with high costs in speed. Reducing the amount of read/write access to your cells will make your code a lot faster.
• Built in functions are (usually) faster than VBA. VBA cannot multi-thread while built-in functions can.

So if we assume 10000 cells and you loop through them this is 10000 read access to your cells. You can reduce that a lot by reading all of them into an array and loop over the array instead. This way you have only 1 read access instead of 10000.

Dim Data() As Variant
Data = Range("A1:A10000").Value  ' read data from range into an array (one read action only)

Dim iRow As Long
For iRow = LBound(Data, 1) To UBound(Data, 1)  ' loop through the array rows
    Debug.Print Data(iRow, 1)  ' process your data here
Next iRow

and if you need to write the data back it can be done with 1 write access for all teh data aswell

Range("A1").Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data
' note that the range and the data need to be exact the same size!

So whether filtering data and looping over the filtered data or looping through the array of the entire data is faster highly depends on the data itself as well as on the filter rules you want to apply. So the only way to find out which one is faster is by trying and measuring the time.

For example if you have 10000 rows and after filtering there is only 3 left then the filter might be faster than looping over the array of 10000 rows.

Answer 2

Return the Values of an Excel Range in Another Office Application

• This is a late-bound solution (note the many As Object declarations) i.e. you can use it as-is, there is no need to create a reference to any library.
• It is assumed that the Excel range starts in cell A1, is contiguous, and has one row of headers.
• Adjust the values in the constants section of the second procedure (GetCriteriaRowsFromExcel) to make it work for you.

Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Using the 'Print2D' procedure, prints the values
'               from the 2D one-based array
'               retrieved by the 'GetCriteriaRowsFromExcel' function
'               to the VBE Immediate window (Ctrl+G).
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub GetCriteriaTEST()
    
    ' Write the 'critical' rows to a 2D one-based array.
    Dim Data As Variant: Data = GetCriteriaRowsFromExcel
    If IsEmpty(Data) Then Exit Sub
    
    ' Print the array's values to the Immediate window ('Ctrl+G')
    Print2D Data, vbTab

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Opens a new instance of Excel, opens a workbook,
'               creates a reference to a worksheet and its current region range.
'               Then it creates a reference to the data range (without headers)
'               and by calling the 'GetCriteriaRows' function, returns
'               a 2D one-based array containing the 'critical' rows,
'               finally closing the instance of Excel.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetCriteriaRowsFromExcel() As Variant
    
    Const wbPath As String = "C:\Test\Test.xlsx"
    Const wsID As Variant = "Sheet1" ' or e.g. 1
    Const Criteria As Long = 5
    Const CritCol As Long = 3
    
    Dim xlApp As Object: Set xlApp = CreateObject("Excel.Application")
    'xlApp.Visible = False ' default is 'False'
    
    On Error GoTo ExcelError
    Dim wb As Object: Set wb = xlApp.Workbooks.Open(wbPath)
    Dim ws As Object: Set ws = wb.Worksheets(wsID)
    ' If the data is contiguous and starts in cell 'A1'.
    ' You may need something different here.
    Dim rg As Object: Set rg = ws.Range("A1").CurrentRegion
    Dim rCount As Long: rCount = rg.Rows.Count
    If rCount > 1 Then
        ' Create a reference to the data range (exclude the headers).
        Set rg = rg.Resize(rg.Rows.Count - 1).Offset(1)
        ' Write the 'critical' rows to a 2D one-based array.
        GetCriteriaRowsFromExcel = GetCriteriaRows(rg, Criteria, CritCol)
    End If
    wb.Close SaveChanges:=False

SafeExit:
    xlApp.DisplayAlerts = False
    xlApp.Quit
    
    Exit Function
    
ExcelError:
    Debug.Print Err.Description
    Resume SafeExit
    
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the values from an Excel range in a 2D one-based array.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetCriteriaRows( _
    ByVal rg As Object, _
    ByVal Criteria As Variant, _
    Optional ByVal CriteriaColumn As Long = 1) _
As Variant
    
    If rg Is Nothing Then Exit Function
    If CriteriaColumn < 1 Then Exit Function ' out of bounds
    
    Dim srg As Object: Set srg = rg.Areas(1)
    Dim cCount As Long: cCount = srg.Columns.Count
    If CriteriaColumn > cCount Then Exit Function ' out of bounds
    
    Dim drCount As Long
    drCount = srg.Parent.Parent.Parent _
        .CountIf(srg.Columns(CriteriaColumn), Criteria)
    If drCount = 0 Then Exit Function ' no matches
    
    Dim srCount As Long: srCount = srg.Rows.Count
    If srCount + cCount = 2 Then Exit Function ' one cell only
    
    Dim sData As Variant: sData = srg.Value
    
    Dim dData As Variant: ReDim dData(1 To drCount, 1 To cCount)
    
    Dim sValue As Variant
    Dim sr As Long
    Dim c As Long
    Dim dr As Long
    For sr = 1 To srCount
        sValue = sData(sr, CriteriaColumn)
        If Not IsError(sValue) Then
            If sValue = Criteria Then
                dr = dr + 1
                For c = 1 To cCount
                    dData(dr, c) = sData(sr, c)
                Next c
            End If
        End If
    Next sr
    
    GetCriteriaRows = dData
        
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      One row at the time, prints the values of a 2D one-based array
'               to the VBE Immediate window (Ctrl+G).
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Print2D( _
        ByVal Data As Variant, _
        Optional ByVal Delimiter As String = ",")
    
    If IsEmpty(Data) Then Exit Sub
    
    On Error Resume Next
    Dim cLower As Long: cLower = LBound(Data, 2)
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    Dim cUpper As Long: cUpper = UBound(Data, 2)
    Dim dLen As Long: dLen = Len(Delimiter)
    
    Dim r As Long
    Dim c As Long
    Dim rString As String
    For r = LBound(Data, 1) To UBound(Data, 1)
        rString = vbNullString
        For c = cLower To cUpper
            rString = rString & CStr(Data(r, c)) & Delimiter
        Next c
        Debug.Print Left(rString, Len(rString) - dLen)
    Next r

End Sub