• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dropdown Contents - Extractable ?

Qseeme

New Member
Hi,

Can anyone create a formula or macro that would extract the individual contents in cells that are to be separated (1 per cell) based on space delimited ? Attachment contains the source. There would be thousands of numbers that it would need to search through. All in column "A".

Thanks.
 

Attachments

Chihiro

Excel Ninja
So... what is the expected result?

Is it each item in dropdown into individual cells, or only the portion to right of space character in individual cell?

If former, something like...
Code:
Sub demo()
Dim obj As OLEObject
Dim ar
For Each obj In Worksheets("Sheet1").OLEObjects
    If obj.progID = "Forms.HTML:Select.1" Then
        ar = Split(obj.Object.DisplayValues, ";")
        Sheet1.Range("A" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row + 1).Resize(UBound(ar)) = Application.Transpose(ar)
    End If
Next
End Sub
 

Qseeme

New Member
Hi Chihiro,

I was not aware until now that there was a reply. My sincere apologies.

This is very good. Only 1 issue.... It is not returning the last row on the contents. I am not sure how to modify the code to allow this to happen.

Thks
 

Chihiro

Excel Ninja
Oh, I forgot to put +1 in Resize code.

Code:
Sheet1.Range("A" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row + 1).Resize(UBound(ar) + 1) = Application.Transpose(ar)
 
Top