Populate Listbox from range in reverse order

[sharky12345]

I'm using this to populate a userform Listbox from a range:

LastRow = Sheet10.Range("C65536").End(xlUp).Row
Set rngSource = Sheet10.Range("D7:F" & LastRow)
 With Me.Listbox1
.ColumnCount = 3
 .ColumnWidths = "80;40;50"
.List = rngSource.Cells.Value
 .IntegralHeight = False
 .Height = 94
.IntegralHeight = True
End With

What I need is for it to populate the listbox in the reverse order from what it currently does - what would be the most efficient method for this?

 

[Fluff]

How about

   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, c As Long
  
   Ary = Sheet10.Range("D7:F" & Sheet10.Range("C" & Rows.Count).End(xlUp).Row).Value2
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = UBound(Ary) To 1 Step -1
      nr = nr + 1
      For c = 1 To UBound(Ary, 2)
         Nary(nr, c) = Ary(r, c)
      Next c
   Next r
   With Me.ListBox1
      .ColumnCount = 3
      .ColumnWidths = "80;40;50"
      .List = Nary
      .IntegralHeight = False
      .Height = 94
      .IntegralHeight = True
   End With

 

[sharky12345]

Perfect, thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback

 

[stemby]

How about

   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, c As Long
 
   Ary = Sheet10.Range("D7:F" & Sheet10.Range("C" & Rows.Count).End(xlUp).Row).Value2
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = UBound(Ary) To 1 Step -1
      nr = nr + 1
      For c = 1 To UBound(Ary, 2)
         Nary(nr, c) = Ary(r, c)
      Next c
   Next r
   With Me.ListBox1
      .ColumnCount = 3
      .ColumnWidths = "80;40;50"
      .List = Nary
      .IntegralHeight = False
      .Height = 94
      .IntegralHeight = True
   End With

Hi, this has been a help to me, and it is just what I need, but I have a question I'm wondering if anyone can help with?

In my source data worksheet there is a date field stored in the dd/mm/yyyy format. However, when I use ths code it displays the date as d/m/yyyy. Is there any way I can tweak things so it diplays the date in the right format dd/mm/yyyy? I'm very new to VBA and not a coder, so any help gratefully received. Thanks.

 

[Fluff]

As you already have a thread running for this topic, you need to stick to it. Thanks

 

[stemby]

As you already have a thread running for this topic, you need to stick to it. Thanks

Sorry, I'm new to this forum. This isn't my thread, but where I first saw the code I have tried to use? Should I not ask a question about the code here?

 

[Fluff]

You need to continue in your existing thread, rather than duplicating your question.

 

[stemby]

You need to continue in your existing thread, rather than duplicating your question.

I'm confused, I've not duplicated my question... In my own thread I asked if there was a way to reverse results in a listbox. One of your users kindly gave me some advice, which I accepted and am going to be investigating.

As he was helping me I came across this bit of code in another thread which seems to have done the trick... However I just needed to know if the code could be tweaked slightly to format the date correctly. I thought that asking in the original thread where the code originated from would be the best place, as to keep continuity with that bit of code? I'm obviously wrong - I'll cut and paste the code back into the other thread then....

 

[Fluff]

However I just needed to know if the code could be tweaked slightly to format the date correctly.

That needs to be in your existing thread, not here.