Excel 2010 / Win 7 / VBA: Feed a SpecialCells range into an Array for UserForm ListBox use

[CtrlAltDel2X]

I have two spreadsheet columns of non-contiguous data.
Column B is the "key" column. VBA rng1 consists of cells where there is text in Column B cells.
Trying to create VBA rng2 which consists of rng1 cells and the adjacent Column A cells.

Next is to then populate two array columns with each row of rng2. The two array columns will eventually fill a UserForm ListBox without blank lines in the second column of the ListBox.

ISSUE: Only the first row of rng2 is populating the listbox.

Thanks for tips!

VBA Code:
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim Arr() As Variant
Dim rng1 As Range
Dim rng2 As Range
Dim LastRow As Integer
Dim R As Long
Dim C As Long


Set ws = Worksheets("test")
LastRow = ws.UsedRange.Rows.Count
Set rng1 = ws.Range("B1:B" & LastRow).SpecialCells(xlCellTypeConstants, xlTextValues)
Set rng2 = Union(rng1.Offset(0, -1), rng1)


Arr = rng2


With ListBox1
.List = Arr
End With


End Sub

 

[Rick Rothstein]

Why not just use the direct route...
[table="width: 500"]
[tr]
[td]

Private Sub UserForm_Initialize()
  Dim Cell As Range
  For Each Cell In Worksheets("test").Columns("B").SpecialCells(xlConstants, xlTextValues)
    ListBox1.AddItem Cell.Value
  Next
End Sub

[/td]
[/tr]
[/table]

 

[CtrlAltDel2X]

Hi Rick, Thanks for the quick reply! I need to preserve Column A data "index-wise" as it correlates to Column B when presented in the 2 column listbox. My desire is when the array is filled, where there is no value in Column A, the array position will reflect that. Once I have the data in the array I'm going to sort both array columns based on column 2 data before populating the listbox(code not shown). I also have selections from "listbox1" moving to "listbox2" (code not shown) and potentially back to "listbox1" and sorted again, the userform is modeless and I'm trying to make sure various userform updates occur quickly in my triggered subs. I didn't want to iterate through potentially thousands of rows, write to the listbox only to pull items out and into an array and then back to the listbox. I was hoping to go from range -> array -> manipulate data ->pass data where it needs to go. Thanks for the reply!

 

[Rick Rothstein]

Does this code get you closer to what you need...
[table="width: 500"]
[tr]
[td]

Private Sub UserForm_Initialize()
  Dim X As Long, TextNotNumbers As Range, Cell As Range, Data As Variant
  Set TextNotNumbers = Worksheets("test").Columns("B").SpecialCells(xlConstants, xlTextValues)
  ReDim Data(1 To TextNotNumbers.Count, 1 To 2)
  For Each Cell In TextNotNumbers
    X = X + 1
    Data(X, 1) = Cell.Offset(, -1).Value
    Data(X, 2) = Cell.Value
  Next
  ListBox1.List = Data
End Sub

[/td]
[/tr]
[/table]

 

[CtrlAltDel2X]

That'll work!!! Much appreciated. Been a while since I've written anything. Had a mental block searching the vast emptiness of my mind for the often illusive one line solution. I had envisioned something like rng2.copy range("C1") would fill the array the way I wanted. Cheers!