Manipulating 3 columns to for an array in VBA


New Member
Jun 8, 2015

I've been trying all sorts of combinations for the past 4 hours and just can't get this right.

Lets say my ultimate goal is to have 3 VBA listboxes. The first is Band, the second is Album, and the third is Song. The user selects one of the ten bands from listbox one, and then listbox 2 populates with albums which match that band. Now they select an album and listbox three displays the songs on that album.

In excel, i have 357 rows by 3 columns. column A lists the bands, column b lists the albums and column c lists the songs.

So how do i get it so that Listbox 1 in vba doesn't show 357bands, rather only 10, listbox 2 understands which albums are tied to the band selected and listbox 3 understands which songs are tied to the album selected?

thank you ever so much.

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Or if anyone has a good 'plain mans english' guide/reference link, i can look that over and keep trying to figure it out....
Upvote 0
Private Sub Userform_Initialize()
Dim oExcel As Excel.Application
Dim oWB As Workbook
Set oExcel = New Excel.Application

  Application.ScreenUpdating = False
  Set sourcedoc = oExcel.Workbooks.Open(FileName:="ihavethisfilledoutinmycodeanditworks")
 Dim arBand()
 Dim arAlbum()
 Dim arSong()
arBand = Range("A2:A358")
arAlbum = Range("B2:B358")
arSong = Range("C2:C358")

  sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
Dim arOnlyBand()
Dim arOnlyAlbum()
Dim arOnlySong()
Dim n, y, z As Integer
Dim x

n = 1
y = 1
z = 1
For x = 1 To 356
    If arBand(x, 1) <> arBand(x + 1, 1) Then
        arOnlyBand(n, 1) = arBand(x, 1)
        n = n + 1
    End If
    If arAlbum(x, 1) <> arAlbum(x + 1, 1) Then
        arOnlyAlbum(y, 1) = arAlbum(x, 1)
        y = y + 1
    End If
    If arSong(x, 1) <> arSong(x + 1, 1) Then
        arOnlySong(z, 1) = arSong(x, 1)
        z = z + 1
    End If
Next x
End Sub

This is what i put together thus far.... and i know it's the farthest thing from what I am looking to do... i originally had arAll as A2:C358 and only that string in the code, but when I was expecting to get back:

arAll('U2','The Joshua Tree','Song1'), arAll('U2','The Joshua Tree','Song2'), arAll('U2','Achtung Baby', Song1) arAll('A Perfect Circle','Danger','Song 1')

I got this back instead:

arAll(1,'U2'), arAll(2,'The Joshua Tree'), arAll(3,'Song 1'), arAll(4,'U2'), arAll(5,'The Joshua Tree'), arAll(6,'Song2'), etc etc.....

And I didn't know what to do with that, so then i figured i would create a range for each of the columns and then get my results and then try to rig up some sort of looping mechnism to put them back together, but it's not working at all like i thought.

I know that the above code is completely wrong... but at this point, I have written and deleted so many different things that I'm just plain confusing myself at this point :/
Last edited:
Upvote 0
Do you mean that because There are lots of songs for each Album and lots of Albums for each Group that columns "A & B" will show Duplicates , in order to show all the songs in column "C" ???
Upvote 0

The usual way is to define different named ranges for each sub-option, but in your case this would mean tens (or maybe hundreds) of them.

A quick and more efficient solution, using your table, is to create 2 auxiliary tables, one for the first column and another for the first 2 columns.

To build these auxiliary tables you can just use the Advanced Filter with the options Copy with No Duplicates.

If you want to try it, I posted a solution here, in post #5:
Upvote 0
MickG, yes your example is perfect... however, I don't understand how you did this. I tried to look at the macro, but didn't see one in there.

The only difference is that I am trying to get my VBA UserForm to pull the data, and then post it just like you have on a UserForm, and not in excel...
Upvote 0

Thanks a million, i was able to follow your link and see your example, and through a little big of rigging, I was able to get the results I was looking for. I believe if a professional coder looked at my VBA code, they would laugh, but hey, it works, and i'm happy. Thanks again!
Upvote 0

Forum statistics

Latest member

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
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 "".
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