title: Excel VBA Listbox - Only Format non blanks as Dates
body: <p>This one has me stumped. I am populating a Listbox with a range and then formatting column 4 as d/mm/yyyy. This works fine if <em>all</em> cells in column 4 have a date. As some cells that are populated into the Listbox are in fact blank the sub crashes when it hits these cells. I have tried various if and else statements to skip the activecell if blank with no luck. </p>
  
  <p>Grateful for any assistance. 
  Alex V</p>
  
  <pre><code> Sub populate_listbox_1()
  
   Dim I As Long
   Dim I2 As Long
   Dim list_count As Long
  
  Dim MyData As Range
      Dim r As Long
      With edit_report_input.compliments_listbox
          .ColumnCount = 17
          .ColumnWidths = "70;300;75;90;80;80;100;0;0;0;0;0;0;0;0;20;0"
          .RowSource = ""
          Set MyData = ActiveSheet.Range("A4:Q498")   'Adjust the range accordingly
          .List = MyData.Cells.Value
          For r = .ListCount - 1 To 0 Step -1
              If .List(r, 1) = "" Then
                  .RemoveItem r
              End If
          Next r
      End With
  
  
  For I = 0 To edit_report_input.compliments_listbox.ListCount - 1
  
         edit_report_input.compliments_listbox.List(I, 4) = Format(DateValue(edit_report_input.compliments_listbox.List(I, 4)), "d/mm/yyyy")
  
  Next I
  
  date_rec_compliment = Format(date_rec_compliment, "d/mm/yyyy")
  
  End Sub
  </code></pre>