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.
Thanks much
Please Login or Register to view this content.
Last edited by duugg; 06-24-2009 at 05:00 PM.
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 Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
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?
ThanksPlease Login or Register to view this content.
Last edited by duugg; 06-24-2009 at 01:39 PM. Reason: better column alignment
Hello Duugg,
Can you post your workbook for review?
Here you go
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.
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.
Hello Duugg,
Runs fine on my computer. I am running Windows XP with Excel 2003.
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
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?
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
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks