I have a dynamic sheet here that I would only like to display to listbox, columns A to F with column A/Name not equal to null/empty cell.
A | B | C | D | E | F |
---|---|---|---|---|---|
Name | Score | Complexity | Points | Total with Points | Total without Points |
Tom | 5 | 3 | 0.25 | 105.00% | 100.00% |
Brenda | 5 | 4 | 0.5 | 110.00% | 100.00% |
Mark | 5 | - | #VALUE! | #VALUE! | |
- | #VALUE! | #VALUE! | |||
- | #VALUE! | #VALUE! |
I tried this below during initialize:
Sub forListBoxShow()
Dim ws As Worksheet, colList As Collection
Dim arrData, arrList, i As Long, j As Long
Set colList = New Collection
Set ws = Worksheets("Sheet1")
arrData = ws.Range("A1:F" & ws.Cells(ws.Rows.count, "A").End(xlUp).Row)
' build collection of row numbers
For i = 2 To UBound(arrData)
If arrData(i, 1) <> vbNullString Then
colList.Add i, CStr(i)
End If
Next
ReDim arrList(1 To colList.count + 1, 1 To UBound(arrData))
For j = 1 To 6
arrList(1, j) = arrData(1, j) ' header
For i = 1 To colList.count
arrList(i + 1, j) = arrData(colList(i), j)
Next
Next
listBoxShow.Clear
With Me.listBoxShow
.ColumnCount = UBound(arrData, 2)
.ColumnWidths = "50,50,70,40,90,90"
.list = arrList
End With
End Sub
But I am getting subscript out of range
error. I also tried to add real values to cells that have #VALUE!
for Name, "Mark", but I have the same error. (For cells with #VALUE! - it has default formula and has purpose). Also tried this for code above but I have the same error:
If Not arrData(i, 1) = "" Then
I appreciate your help.
The error subscript out of range
usually occurs when the array index exceeds the declared size of the array. UBound(arrData, 2)
should be used in ReDim
as the maximum value of the second dimension.
ReDim arrList(1 To colList.Count + 1, 1 To UBound(arrData, 2))