Excel array Populated with non blank values of listobject column range

Question

I have a column of a list object with some non empty values at the beginning. Just assume the first 15 values are not blank.

I know it is possible to pass the values of a range to an array like this:

Dim mylistObject As ListObject
    Set mylistObject = ThisWorkbook.Sheets("training").ListObjects(1)

Dim theArray() As Variant
   theArray = mylistObject.listcolumn(1).DataBodyRange.value

The question is how can I pass only the non blank values. I know how to do it with loops but the key point here is speed, if the listobject has hundreds of rows and the operation is done tens of times it takes too long.

I also know that it might be possible to calculate the number of non blank cells and redim the array accordingly and loop through values. still not elegant.

Any idea? there should be a way to tell in VBA language

mylistObject.listcolumn(1).DataBodyRange.value
' but not all the range but the non empty ones.

Thanks a lot

You could filter the blanks out, copy that range and transfer to an array. — SJR, Aug 09 '19 at 10:00
Thant works. I find filtering very contra intuitive. Perhaps I should change my mind — JFerro, Aug 09 '19 at 12:42
It can be a useful technique so might be worth overcoming your reluctance if you are determined to avoid loops. — SJR, Aug 09 '19 at 13:22
For the sake of the art: Posted a late & hopefully helpful answer just to show you an alternative using the advanced features of the `Application.Index` function :-) @Berlines — T.M., Oct 22 '19 at 20:01

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

Using the possibilities of the Application.Index function

Demonstrate an easy approach to create and transform the listbox'es column data Array:

Get all data of first column (including blanks) as already shown in the original post (BTW the correct syntax in the array assignment is theArray = mylistObject.ListColumns(1).DataBodyRange.Value with a final "s" in .ListColumns)
Eliminate blank row numbers using the advanced features of the Application.Index function and a subordinated function call (getNonBlankRowNums())

Basic transformation syntax by one code line:

   newArray = Application.Index(oldArray, Application.Transpose(RowArray), ColumnArray)

where RowArray / ColumnArray stands for an array of (remaining) row or column numbers.

Related link: Some peculiarities of the the Application.Index function


Sub NonBlanks()
  ' Note: encourageing to reference a sheet via CodeName instead of Thisworkbook.Worksheets("training")
  '       i.e. change the (Name) property in the VBE properties tool window (F4) for the referenced worksheet
  '       (c.f. https://stackoverflow.com/questions/58507542/set-up-variable-to-refer-to-sheet/58508735#58508735)
    Dim mylistObject As ListObject
    Set mylistObject = training.ListObjects(1)
    
  ' [1] Get data of first column (including blanks)
    Dim theArray As Variant
    theArray = mylistObject.ListColumns(1).DataBodyRange.Value   ' LISTCOLUMNS with final S!!

  ' [2] eliminate blank row numbers
    theArray = Application.Index(theArray, Application.Transpose(getNonBlankRowNums(theArray)), Array(1))

End Sub

Function getNonBlankRowNums(arr, Optional ByVal col = 1) As Variant()
' Purpose: return 1-dim array with remaining non-blank row numbers
  Dim i&, ii&, tmp
  ReDim tmp(1 To UBound(arr))
  For i = 1 To UBound(arr)
      If arr(i, col) <> vbNullString Then   ' check for non-blanks
          ii = ii + 1                       ' increment temporary items counter
          tmp(ii) = i                       ' enter row number
      End If
  Next i
  ReDim Preserve tmp(1 To ii)               ' redim to final size preserving existing items
' return function value (variant array)
  getNonBlankRowNums = tmp
End Function

Excel array Populated with non blank values of listobject column range

1 Answers1