Hi,
I have seen part of what I need but not all of it.
Sheet1, Column A contains thousands of values, including blanks, with many values duplicated, in no particular order.
Sheet2, Column A needs to contain a sorted (ascending) list of unique/distinct values, excluding blanks, from sheet 1.
Can someone suggest a soluton?
Thanks!
Change xlNo to xlYes if you have header.
Please Login or Register to view this content.
A non-VBA solution would be to copy all data from A to B, then use excel's "remove duplicates" option (data/Data Tools), and then sort what remains
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I need to automate this so that it extracts and sorts when the sheet is opened by the user.
Please Login or Register to view this content.
Hi Jindon,
Perfect! Marvelous!
Domo Arigato!
tom
Hey Jindon,
One glitch:
I have formulas in the receiving spreadsheet so while I see the reasoning in clearing the contents, I need to make it happen only to column A.
Try change
toPlease Login or Register to view this content.
Please Login or Register to view this content.
Hmmm, I must be doing something wrong. I changed the line but upon saving then restarting, the column does not seem to have been erased (i put some bogus data below where the good stuff ended).
Can you upload your workbook?
Probably not...it is over 3mb. I will put a sample together and upload shortly.
Here ya go, Jindon.
Jindon, the file I sent was missing the End Sub, which I know you will see immediately. It populates column A the first time, but when i delete some entries and add sume extra stuff down below, save and reopen, it does not work. hope you are seeing what I am seeing. tom
It works like a champ when I execute from VBE. The problem seems to be executing upon activation.
Last edited by snapfade; 04-08-2013 at 08:36 PM.
It should be placed in Sheet2 code module.
Changed a bit for speeding up.
Please Login or Register to view this content.
Jindon, I figured it out. Had to put the code in Sheet2. Beautiful. Thanks ever so much for your help. I really appreciate it.
tom
Hey Jindon,
If I have columns B through S with formulas on sheet2 and want them to be copied to all the rows where the extracted values appear, how would I do that? assuming that the row of formulas in row one is valid if copied to all rows.
I promise, no more questions after this! ;-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks