Results 1 to 8 of 8

New to arrays-how do I build new array while filtering first array?

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Question New to arrays-how do I build new array while filtering first array?

    Please see code below. I am struggling with building the new array for the listbox on the form.

    (Unless it would be faster/more efficient just to filter & delete values in the original array?? But I don't know how/if this is even possible)

    Private Sub EmployeeList_Populate()
        sWbSheet = "employee10"
    
        Dim intMaxRow As Integer
        Dim intMaxCol As Integer
        Dim varMainArray As Variant
    
        ' turn screen updating off (prevent the user from seeing the source workbook being opened)
        ' open the source workbook as ReadOnly
        Application.ScreenUpdating = False
        Set wbSource = Workbooks.Open(sServerFolderProgram & sServerFileDatabaseExcel, False, True)
    
        ' create array from worksheet range
        With wbSource
            With .Worksheets(sWbSheet)
                .Activate
                intMaxRow = .Range("A1").End(xlDown).Row 'there are no blanks in row 1 data
                intMaxCol = .Range("A1").End(xlToRight).Column 'there are no blanks in column A data
                varMainArray = .Range(Worksheets(sWbSheet).Cells(1, 1), Worksheets(sWbSheet).Cells(intMaxRow, intMaxCol)) 'used range starting from A1
            End With
            .Close False ' close the source workbook without saving changes
            Set wbSource = Nothing
        End With
    
    
    '   build new array
        Dim varNewArray As Variant
        Dim i As Integer
        For i = LBound(varMainArray) To UBound(varMainArray)
           If varMainArray(i, 2) = int_employer_id Then
    
        Help NEEDED!
        array is expected to be 5 columns wide by variable rows deep
        I need to check if column 2 (B) value is = int_employer id.
        If yes then add values from column 1 (A), column 3 & 4 (C & D) to a new array (3 columns by X rows deep)
    
    
              'varNewArray = varMainArray(i, 1)
           End If
        Next i
    
    
    '   populate listbox
        Me.ListBox1.List = varNewArray
        Application.ScreenUpdating = True
    End Sub
    Last edited by mc84excel; 05-02-2013 at 06:10 PM. Reason: Correct code xlRight to xlToRight
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1