I'm trying to learn VBA and macros the best I can, and it's going rather slow.
I'm trying to create a macro that will search through a column of information (200 + cells) and paste 'unique records' (non-duplicated data) into another column of the same sheet.
I came accross this thread http://www.excelforum.com/excel-prog...ank-macro.html
The previously stated thread I thought would be beneficial but i've had some issues with it.
For information
My SrcRng will be (C9:C225)
My DstRng will be (N9)
Last edited by garretonufer; 07-30-2009 at 05:18 PM.
Hello garretonufer,
This macro will copy the unique values in column "C", starting at "C9" down to the last entry in column "C", over to the destination range, on the same worksheet, starting with "N9". You can change the worksheet name and these starting cell addresses if you need to. They are mark in red. Although this code appears long, it is very fast. This will run will Excel 2000 and up.
Adding the Macro
Please Login or Register to view this content.
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.
To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
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!)
Thank you, i'll post up how it worked out.
This has worked but only to an extent.
I have roughly 220 cells of information in Column "C"
some of the data is duplicated in another row.
I want this macro to omit all information that has duplicates when copying the data to "N9"
Currently it's only removing the duplicated data showing one of every piece of data.
ie: if C9 =9002
and C10 =9002
I want both cells to be omitted when copying to N9
currently when
C9 = 9002
C10=9002
the macro seems to not copy C10 but still shows the data of 9002 in the N9 results column
I've seen on another thread here of a toggle button. Can i be educated and find a way to incorporate a toggle button to show the desired data, and hide it.
Hello garretonufer,
I misunderstood what you wanted. This version of the macro will copy an entry over into column "N" only if it is the only one.
Please Login or Register to view this content.
I've been running into issues.
I appreciate the update, however it's not acting the same way.
I see that the DstRng is N9 however the macro is inputing data in N8
the other issue is that it's only ouputting 1 cell of data and it is the last cell in the column. And i know there are more than 1- Non-duplicate. I've attached the spreadsheet i'm working with.
the "Copy Uniques 1" button is the first macro you posted, the "Copy Uniques 2" is the second macro. the only thing i changed what the name of the sheet.
Hello garretonufer,
Thanks for posting the workbook. This will save a lot of time.
Hello garrentonufer,
I revised the macro. This returns only unique entries from column "C". Entries that occur only once. This has been added to the attached workbook.
Please Login or Register to view this content.
Excellent. that works perfect.
If i wanted to duplicate the macro for a different SrcRng and DstRng on the same worksheet. do i just need to change the
Set DstRng = Wks.Range("O9")
Set SrcRng = Wks.Range("C9")
lines? or is there something else that would need adjustment?
Hello garretonufer,
Yes, that is all you need to change. The end of each range is determined automatically by the macro so all need to supply the starting celll of the range.
I tried doing exactly that and it's not filtering out the duplicates for the second macro. see attached file.
Hello garretonufer,
The problem is you have mixed data types in your frequencies. Some are numbers and some are text. Are these entered by hand or from another file? For the comparison to work properly, all the entries need to be the same data type.
They are entered by hand. but i only see numerical values in the D column. Am I not seeing something that you are?
Forgive my ignorance. I was going back through the D column and realized that the cell format was set up to round the values, so even though tye appear to be the same on the surface really the values didn't match. I've made that change and the macro works great. thanks for your help.
I'll set the topic to solved.
Hello ,
I am glad you got it working and thanks for marking the thread solved. You would see what I saw with entries unless you were examining them with VBA code. All data in a cell is stored a Variant data type. Using the VBA function VarType, I can tell exactly how Excel sees the data. Even though, the entries appear numeric, they all weren't stored that way.
i just noticed that if
D9 showed 608.0250 the actual value in the cell was 608.0249999997
and therefore would not match D10 that showed 608.0250 when it's actual value was 608.0250
once in went through and cleaned up all the chaos, the macro worked like a champ. now the thoughts are to have the macro do more. but i'll reserve that for a different thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks