Array Question

[TheShaunMichael]

Hi all,

Have an excel sheet I use to create estimates (each new estimate goes in row 2). Each time I create a new estimate, a row is inserted into column 2 pushing all previous records down. I have about 800 rows now. For each estimate/row, the company name is in Column A.

Ultimately, I need to create an array that returns a list of all unique company names. The challenge is, in my list of 800 estimates, company names in some cases appear 30 or 40 times whereas others appear just once.

I obviously need some type of If then loop to test for existing values within an array. If the value in column A doesn't exist, the value needs to be added, and if it does exist the loop needs to move to the next row.

Many thanks!

 

[Norie]

Do you have headers in the data?

If you don't that could be one reason why you get repeats - the first company is being treated as the header and is also a value in the list.

So you see that company repeated, once as the header and once as a unique item on the list.

To populate a listbox on a userform with unique, sorted items from column A of a worksheet you can try this.

Private Sub UserForm_Initialize()
Dim wsTemp As Worksheet
Dim wsData As Worksheet
Dim LastRow As Long
Dim rng As Range
 
    Set wsData = Worksheets("Contracts")  ' name of worksheet where company names are
 
    Set wsTemp = Worksheets.Add
 
    LastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row
    ' header
 
    wsData.Range("A1:A" & LastRow).AdvancedFilter xlFilterCopy, , wsTemp.Range("A1"), Unique:=True
 
    wsTemp.Sort.SortFields.Add Key:=wsTemp.UsedRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 
    With wsTemp.Sort
        .SetRange wsTemp.UsedRange
 
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
    Set rng = wsTemp.Range("A2")
 
    Set rng = Range(rng, wsTemp.Range("A" & Rows.Count).End(xlUp))
 
    ListBox1.List = rng.Value
 
    Application.DisplayAlerts = False
 
    wsTemp.Delete
 
    Application.DisplayAlerts = True
 
End Sub

The above will work if there are headers, if there aren't then a little more work is needed but it can be done.

PS If you do need an array that can be done too.

 

[TheShaunMichael]

Ahh yes. I took care of the header issue by having the NamesOut variant begin in row 2 instead of 1. Thanks for tipping me off!

Still though, my list drops off about 1/4 way through the list. I'm not having an issue of getting things into the list box. I've got all my companies beginning with A, B, C and some beginning with D. Then it's just white...

 

[Norie]

The code I posted shouldn't include any spaces.

If you set a breakpoint (F9) just after the filter what do you see in the tempory file?

Also, what do you see when you type ?rng.Address after stepping through the code (F8) up to the point there the listbox List is set.

 

[TheShaunMichael]

In debugging here is where things go awry:

.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Addr = .SpecialCells(xlCellTypeVisible).Address
ActiveSheet.ShowAllData
Range(Addr).Copy Cells(1, UnusedColumn + 1)



The advanced filter line takes the list of ascending company names and makes visible only unique names (by hiding rows that contain non-unique values).

The Addr line then goes about copying just those visible rows.

The show all data function of course unhides the rows.

Finally, the line beginning with Range(Addr) is supposed to copy the unique values into the adjacent cell. BUT - it only copies the first 50 or so values. Which is why my listbox only contains about 50 rows of data.

Any idea why not all of the values in the Addr line are getting copied?

 

[Norie]

Sorry, that's not my code so I can't really comment.

 

[TheShaunMichael]

Hi Norie,

Replaced the code I had which was half working with yours and it worked perfectly! Thanks for your help.

Also - any idea how to enable the scroll function of my mouse within the listbox? It doesn't work...

Again - thanks to everybody! Learned a lot here.

Shaun

 

[Norie]

Shaun

Don't know about the mouse thing - doesn't work for me.

I think it could actully be down to your mouse/mouse drivers etc.

I can use the scroll button in some places but not in others, never been able to work out any 'logic' behind where it will/won't work though.

 

[Rick Rothstein]

You have working code now, so I am happy for you on that score, but I am still curious as to why you had problems with my code (which worked perfectly in my tests). To satisfy my curiosity, I was wondering if you would be willing to send your workbook (with the original in it) to me so I can see where my code is going wrong? If the answer is yes, my email address is rickDOTnewsATverizonDOTnet... just replace the upper case letters with the symbols they spell out.

 

[TheShaunMichael]

Hi Rick,

Yes I'm curious to know why yours had problems in mine as well. I can't send the entire workbook as I have sensitive data there but would copy & pasting the module here work? I can try recreating the issue in a dummy workbook also.

Does it have anything to do with limits on the constants defined? I forget but how many characters are allowed in a String constant?

Shaun

 

[Rick Rothstein]

Yes I'm curious to know why yours had problems in mine as well. I can't send the entire workbook as I have sensitive data there but would copy & pasting the module here work? I can try recreating the issue in a dummy workbook also.

Does it have anything to do with limits on the constants defined? I forget but how many characters are allowed in a String constant?

I think the problem is in your data, not the code you have so far... but that is just a guess. Can you make a copy of the workbook and remove most of the columns of sensitive information without destroying what the code we are talking about does? In other words, keep deleting information until my code stops failing to work, then back up a step and then send me that resulting workbook. By the way, just so you know, as a Microsoft MVP, Microsoft trusts me with their sensitive non-public data (of course I signed a Non-Disclosure Agreement to bind me), so I promise I won't misuse your data if you wanted to send me your original workbook.