+ Reply to Thread
Results 1 to 12 of 12

VBA to go through entire list and generate a list of names

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    US
    MS-Off Ver
    2013
    Posts
    67

    VBA to go through entire list and generate a list of names

    I have a list of names in Column K, maybe 25 different names all repeating and broken up amongst 600 rows. I want VBA to scan through the column and create a new list of all the names in the list without repetition. For instance there may be 20 rows in a sequence of one company, the list is scattered and broken up with spaces etc.

    So I would think it would have to go through each row down the column and check the list that is made somewhere else if it is not on the list then to add it to the list.

    I want it to stop running through the Column K when it hits the word "End".

    I understand that I will have an
    Please Login or Register  to view this content.
    That is very crude but is this the right way to do it. The CopyToList is some generic list that the program would make of the company names maybe in Sheet1.Column X

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to go through entire list and generate a list of names

    Try Pivot Table or advance filter. Better if you could attach a sample with a desired result.

  3. #3
    Registered User
    Join Date
    05-30-2013
    Location
    US
    MS-Off Ver
    2013
    Posts
    67

    Re: VBA to go through entire list and generate a list of names

    The document is attached. As you can see I would like it to stop at the word END and copy the names into the column L on Sheet1.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-30-2013
    Location
    US
    MS-Off Ver
    2013
    Posts
    67

    Re: VBA to go through entire list and generate a list of names

    I would rather work the problem with VBA so that way it can be initiated with the rest of the command.

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to go through entire list and generate a list of names

    Try the attached.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-30-2013
    Location
    US
    MS-Off Ver
    2013
    Posts
    67

    Re: VBA to go through entire list and generate a list of names

    Please Login or Register  to view this content.
    I get an error: Compile Error: Expected Array on the Ubound portion
    Last edited by DadaaP; 10-23-2013 at 03:26 PM.

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to go through entire list and generate a list of names

    K1,not k
    Please Login or Register  to view this content.
    .
    I also did not dim I as integer, because once the code hits 32647 rows, you will come back to me with another overflow error.

  8. #8
    Registered User
    Join Date
    05-30-2013
    Location
    US
    MS-Off Ver
    2013
    Posts
    67

    Re: VBA to go through entire list and generate a list of names

    Please Login or Register  to view this content.
    The way it sits it returns an Expected Array on the Ubound portion. IF I change UBound to UBound(K) it then has a problem with the x=sheets line.

  9. #9
    Registered User
    Join Date
    05-30-2013
    Location
    US
    MS-Off Ver
    2013
    Posts
    67

    Re: VBA to go through entire list and generate a list of names

    I tried declaring X as a variant to no avail. If i remove the Dim X all together I receive an error Application defined or Object defined error on the X= line.

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to go through entire list and generate a list of names

    I do not know where are you getting the data. Please double check my code. There is no where in my code which says k and X is declared as range.

    Please Login or Register  to view this content.
    Last edited by AB33; 10-23-2013 at 04:45 PM.

  11. #11
    Registered User
    Join Date
    05-30-2013
    Location
    US
    MS-Off Ver
    2013
    Posts
    67

    Re: VBA to go through entire list and generate a list of names

    Sure enough sir/madam you are spot on. I have gone and changed my errors and am extremely thankful for your help AB33.

    Thank you for sticking with me! I am a little shakey on arrays however I know your code is extremely shorter than the one I would of made...

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to go through entire list and generate a list of names

    My code is very long and complex.
    Advance filter will do it in 2 lines. Please try recording a macro code and see how easy it is.

    Please mark the thread as "Solved"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Generate list of names based on multiple criteria
    By mattadler22 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-03-2013, 06:02 PM
  2. Replies: 15
    Last Post: 11-28-2012, 03:23 PM
  3. Auto Generate List of Names?
    By rack04 in forum Excel General
    Replies: 3
    Last Post: 12-24-2011, 01:04 AM
  4. How to generate a list of tab names in a summary sheet
    By deanJ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-13-2009, 06:16 PM
  5. Help To Generate List of WorkSheet Names
    By mmcginnis in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-06-2005, 08:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1