title: How to populate a listbox with a dynamic range of cells in VBA/Excel
body: <p>I've gone through several similar questions in hopes to solve my answer, but I've been struggling to connect the specific questions with my scenario.</p>
  <p>I have a worksheet that has a button that opens a userform - this code runs in a standard module. Once the userform is open, I'd like to populate a listbox with a data from a range of cells that may vary with each spreadsheet.</p>
  <p><a href="https://i.stack.imgur.com/v9ZJ9.png" rel="nofollow noreferrer">Here is the userform:</a></p>
  <p>I know I have successfully grabbed my dynamic range of cells because I've been able to select the range with my code. Here is my code for the dynamic range:</p>
  <pre><code>Private Sub UserForm_Initialize()
  
  Dim xrange, newStartCell As Range
  Dim last_col As Integer
  Dim rng As Range
  
  Me.StartUpPosition = 0
  Me.Top = 300
  Me.Left = 475
  
  ' find cell with row header &quot;cygnet code&quot;
  Set xrange = Cells.Find(What:=&quot;cygnet code&quot;, After:=ActiveCell, LookIn:=xlFormulas2, _
      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)
  
  Set newStartCell = xrange.Offset(, 1)
  
  'This line gets the last column
  last_col = Cells(4, Columns.Count).End(xlToLeft).Column
  
  With ThisWorkbook.Sheets(&quot;C&amp;E&quot;)
      Set rng = .Range(.Cells(newStartCell.Row, newStartCell.Column), .Cells(newStartCell.Row, last_col))
  End With
  
  With Me.ListBox1
      .List = rng.Value
  End With
  
  End Sub
  </code></pre>
  <p>I don't know if it has anything to do with running this code in the initialize function or if the way I'm grabbing the range is not compatible with the listbox? In either case, the code runs without &quot;error&quot; but the listbox remains blank (it adds one blank row).</p>
  <p>Here is the code I used to test the selection tool working:</p>
  <pre><code>    With ThisWorkbook.Sheets(&quot;C&amp;E&quot;)
      Set rng = .Range(.Cells(newStartCell.Row, newStartCell.Column), .Cells(newStartCell.Row, last_col))
      .Range(.Cells(newStartCell.Row, newStartCell.Column), .Cells(newStartCell.Row, last_col)).Select
      End With
  </code></pre>
  <p>Thanks for your help in advance!</p>