+ Reply to Thread
Results 1 to 13 of 13

Code change below to find the Customer column by name rather than column letter

  1. #1
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Code change below to find the Customer column by name rather than column letter

    I have this macro below and would like the code changed to find the "Customer Number" column by name rather than by column B. Note that the "Customer Number" column will always be somewhere in row 1.


    Please Login or Register  to view this content.
    Thanks much
    Last edited by duugg; 06-24-2009 at 05:00 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Code change below to find the Customer column by name rather than column letter

    Hello duugg,

    This macro will look in row 1 for the column header "Customer". It then uses the contents of row 2 in that column as the search criteria. Every row after that will be deleted if it matches the search criteria.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Code change below to find the Customer column by name rather than column letter

    Leith,

    Thanks for the macro but it's only deleting certain duplicate customer number rows. In the example below, it only deleted the duplicate customer in row 2, but didn't delete the duplicate customer in row 5. Any thoughts?


    Please Login or Register  to view this content.
    Thanks
    Last edited by duugg; 06-24-2009 at 01:39 PM. Reason: better column alignment

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Code change below to find the Customer column by name rather than column letter

    Hello Duugg,

    Can you post your workbook for review?

  5. #5
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Code change below to find the Customer column by name rather than column letter

    Here you go
    Attached Files Attached Files

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Code change below to find the Customer column by name rather than column letter

    Hello Duugg,

    I fixed the problems here is the new macro. I installed it already in the attached workbook.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Code change below to find the Customer column by name rather than column letter

    Leith,

    It still didn't work. I'm getting the

    " Runtime error 91 Object variable with block variable not set"

    error message on this line

    Please Login or Register  to view this content.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Code change below to find the Customer column by name rather than column letter

    Hello Duugg,

    Runs fine on my computer. I am running Windows XP with Excel 2003.

  9. #9
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Code change below to find the Customer column by name rather than column letter

    Leith,

    Ahhh..

    I forgot to change my header, once I changed it, it worked, but with a glitch...it only deleting duplicate customers if the duplicate cells DIRECTLY below each other. Check yours out and let me know if this is the case for you as well.

    Any thoughts?

    Thanks for your patience and help on this

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Code change below to find the Customer column by name rather than column letter

    Hello Duugg,

    In the future to avoid all this Q and A, please be specific in what your requirements are. I have had to guess a lot about what you want based on information that is incomplete.

    As I understand it so far, you are looking to remove all duplicate customer numbers, regardless of how they are ordered in the table. All duplicate customer numbers found will delete the entire row and the remaining rows will shift up. Is there any reason a new table can not be created with only the unique customer numbers?

  11. #11
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Code change below to find the Customer column by name rather than column letter

    Leith,

    Sorry, I am new to this VBA and have learned a great deal from a lot of very nice people including yourself and I am very very appreciative for that

    For this particular post, I "thought" (my fault for thinking that) that there were probably 1 or 2 lines of code that needed to be changed so that the macro would look for "Customer" column rather than "B" column. It is obviously not that easy, sorry for that.

    Yes, unfortunately, this has to be in the same worksheet.

    In my first post, the macro I posted had code that worked perfectly EXCEPT that the customer number HAD to be in column B.

    Yes, I want to delete and shift the entire row up if the customer number column contains a duplicate customer number in more than one row. Even if both rows aren't exactly duplicate in the other columns, I still want to delete the entire duplicate row/rows.

    It doesn't matter which row is kept and long as one remains. But the duplicate customer numbers can be in several rows throughout the spreadsheet. I could have a customer number of 505 in row 20, then the same customer number in row 44, then again in row 1500. It will vary. But I only want to keep 1 row.

    Thanks again and hope this clears it up

  12. #12
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Code change below to find the Customer column by name rather than column letter

    Hello Duugg,

    This macro, although longer, is faster and more efficient. Looks like you need this if you have a lot of data to process. The macro, along with a button, has been added to the attached worksheet.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Smile Re: Code change below to find the Customer column by name rather than column letter

    Leith,

    WwwwowwwwwwwWWWWW!

    Unreal, complete perfection!

    THANK YOU SOOO MUCH, THIS IS ABSOLUTELY PERFECT!!!

    I am closing this thread now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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