Populating List Box with An Array - Reverse the Order?

eddiev1972

New Member
Joined
Mar 22, 2010
Messages
38
Thank you for in advance for any assistance. There is probably a very easy answer to this question and I just don't see it.

I am populating a list box with a 2 dimensional array: Data(1 to 3, 1 to x). The data I am using is 3 categories (period name, revenue, net operating income). The number of periods varies based on if the cell is populated with data.

I get everything to work, however, the data in the list box is displayed from the oldest to the newest and I want it to run in the reverse order (newest to oldest) I can't seem to get it to work. Below is the code that is working from oldest to newest

Code:
Sheets("Revenue").Activate
 
CountDown = Application.WorksheetFunction.CountA(Range("a:a"))
Set SearchingRange = Range(Cells(3, 1), Cells(CountDown, 1))
Set FindHotel = SearchingRange.Find(HotelName)
 
CountRight = Application.WorksheetFunction.CountA(Range("2:2"))
For Each Cell In Range(FindHotel, FindHotel.Offset(0, CountRight))
If Cell.Value <> "" Then
ActiveRight = ActiveRight + 1
End If
Next Cell
 
ReDim Data(1 To ActiveRight, 1 To 3)
 
For i = 1 To ActiveRight
Data(i, 1) = Range("a2").Offset(0, i)
Next i
 
For i = 1 To ActiveRight
Data(i, 2) = FormatCurrency(FindHotel.Offset(0, i))
Next i
 
Sheets("Gop").Activate
 
CountDown = Application.WorksheetFunction.CountA(Range("a:a"))
Set SearchingRange = Range(Cells(3, 1), Cells(CountDown, 1))
Set FindHotel = SearchingRange.Find(HotelName)
 
For i = 1 To ActiveRight
Data(i, 3) = FormatCurrency(FindHotel.Offset(0, i))
Next i
 
ListBox1.ColumnCount = 3
ListBox1.List = Data

When popoulating the Data array I have tried

Code:
for i = ActiveRight to 1 step -1
Data(i, 3) = FormatCurrency(FindHotel.Offset(0, i))
next i

But that gives the same results. Thank for any suggestions.
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try:
Code:
For i = 1 To ActiveRight
    Data(ActiveRight + 1 - i, 3) = FormatCurrency(FindHotel.Offset(0, i))
Next
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top