0

I am trying to populate a listbox from a list of items, I can get it to populate but it is taking in my header row as a row in the list and the headers are blank. I am not sure where I am going wrong. Any help would be great.

Sub populateList()
  Dim rngName As Range
  Dim ws As Worksheet
  Dim i As Integer
  Set ws = Worksheets("ProjectData")
  lbTasks.Clear
  lbTasks.ColumnHeads = True
  lbTasks.ColumnCount = 10
  Dim LastRow As Long
  LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
  For i = 1 To LastRow
    If ws.Cells(i, 1).Value <> vbNullString Then lbTasks.AddItem ws.Cells(i, 1).Value
    If ws.Cells(i, 2).Value <> vbNullString Then lbTasks.List(i - 1, 1) = ws.Cells(i, 2).Value
    If ws.Cells(i, 3).Value <> vbNullString Then lbTasks.List(i - 1, 2) = ws.Cells(i, 3).Value
    If ws.Cells(i, 4).Value <> vbNullString Then lbTasks.List(i - 1, 3) = ws.Cells(i, 4).Value
    If ws.Cells(i, 5).Value <> vbNullString Then lbTasks.List(i - 1, 4) = ws.Cells(i, 5).Value
    If ws.Cells(i, 6).Value <> vbNullString Then lbTasks.List(i - 1, 5) = ws.Cells(i, 6).Value
    If ws.Cells(i, 7).Value <> vbNullString Then lbTasks.List(i - 1, 6) = ws.Cells(i, 7).Value
    If ws.Cells(i, 8).Value <> vbNullString Then lbTasks.List(i - 1, 7) = ws.Cells(i, 8).Value
    If ws.Cells(i, 9).Value <> vbNullString Then lbTasks.List(i - 1, 8) = ws.Cells(i, 9).Value
    If ws.Cells(i, 10).Value <> vbNullString Then lbTasks.List(i - 1, 9) = ws.Cells(i, 10).Value
  Next i
End Sub
Terence
  • 59
  • 2
  • 9
  • 1
    You can only use `ColumnHeads` if the listbox is populated using a range address in the `Rowsource`/`ListFillRange` (depending on where the listbox is). – Rory Aug 18 '20 at 09:13
  • I have created a user form that holds the Listbox. So how else can I populate the headers, the header row is visible in the listbox but has no text – Terence Aug 18 '20 at 09:26
  • As I said, you have to put the data into a range on a sheet, then specify the RowSource as that range's address, excluding the header row. – Rory Aug 18 '20 at 09:47
  • Is there anyway I can ignore the header from being added to the list? – Terence Aug 18 '20 at 10:05
  • You could capture the range that has the values and then delete empty rows from the range ([see this on how to delete empty rows from a range](https://stackoverflow.com/questions/49045885/delete-blank-cells-in-range)) before using the range to populate the listbox – Zac Aug 18 '20 at 10:27
  • Change the loop to start at 2 not 1. – Rory Aug 18 '20 at 10:27
  • Have you considered using a ListView control? – TinMan Aug 18 '20 at 11:37

0 Answers0