none
VBA (Apply Array in filtered range in excel) RRS feed

  • Question

  • Hi All,

    We are working in huge data in excel which is the reason I have opted for Array method in VBA. While Applying array output to a continuous range of cells the array output gets printed without any issue in seconds.

    For instance, no filtered cells

    Range("A1:A100000") = arrayoutput()

    But in case if we have applied filters and do the same code as mentioned above the arrayoutput breaks in the first hidden cells and continue to print an incorrect value to the cells after that.

    There is no toggle filter in excel similar to access. if option is present I can simply remove filter apply the array value and toggle filter to the user's criteria back.

    Doing loop update cell by cell for large number of rows counts is really slower where the excel goes not responding. Do anyone have any idea of applying array values to the filtered range but should not be done in loop.

    Thanks,

    Prabhu Murugan

    Thursday, March 14, 2019 11:47 AM

All replies

  • Copy the visible cells to a temporary worksheet and then assign the range to array. Example code as per the following.

    Sub AssignNonContiguousRangeToArray()
        Dim wsData As Worksheet
        Dim wsTemp As Worksheet
        Dim rngFiltered As Range
        Dim arrData As Variant
        Dim i As Long
       
        Set wsData = ThisWorkbook.Worksheets("Sheet1")
       
        Set wsTemp = Worksheets.Add(After:=Worksheets(Worksheets.Count))
       
        With wsData.AutoFilter.Range
            If .Columns(1).Cells.Count > 1 Then     'If not greater than 1 then no data displayed under column headers
                'Following assigns the data only to the range variable (without the column headers)
                Set rngFiltered = .Columns(1) _
                                    .Offset(1, 0) _
                                    .Resize(.Rows.Count - 1, 1) _
                                    .SpecialCells(xlCellTypeVisible)
            Else
                MsgBox "No data to copy. Procesing terminated."
                Exit Sub
            End If
        End With
       
        With wsTemp
            rngFiltered.Copy Destination:=.Cells(1, 1)
            arrData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        End With
       
        'Following loop for testing array contents.
        For i = 1 To UBound(arrData)
            Debug.Print arrData(i, 1)
            If i = 10 Then
                i = UBound(arrData) - 10    'For testing and don't want to output entire array
            End If
        Next i
       
        'Following optional until finished processing.
        'Might just want to clear and re-use for further processing
        Application.DisplayAlerts = False
        wsTemp.Delete
        Application.DisplayAlerts = True
           
    End Sub


    Regards, OssieMac

    Tuesday, April 2, 2019 3:50 AM