|
Getting unique entries from an array/column
Hi, I would like to make an array of unique entries from another array or column(preferably a dynamic range), and then paste back to the worksheet on a different sheet. How do i do this with the least possible code? -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=564651 |
Getting unique entries from an array/column
Sub CopyData() Columns("B:B").Insert Range("A:A").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("B1"), _ Unique:=True Columns("B:B").Copy Sheets("Sheet2").Range("A1") Columns("B:B").Delete Shift:=xlToLeft End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "erikhs" wrote in message ... Hi, I would like to make an array of unique entries from another array or column(preferably a dynamic range), and then paste back to the worksheet on a different sheet. How do i do this with the least possible code? -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=564651 |
Getting unique entries from an array/column
If you have a header in that column:
Columns(1).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=worksheets("Sheet2").Range("A1"), _ Unique:=True If not, temporarily put a header there, then remove it after your done. (using code). -- Regards, Tom Ogilvy "erikhs" wrote: Hi, I would like to make an array of unique entries from another array or column(preferably a dynamic range), and then paste back to the worksheet on a different sheet. How do i do this with the least possible code? -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=564651 |
Getting unique entries from an array/column
Just to add to Bob's excellent advice:
Bob appears to be under the mistaken impression that advancedfilter can't copy to another sheet. That is incorrect. It can. Use of the dummy column B is not required. Sub CopyData() Range("A:A").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Sheets("Sheet2") _ .Range("A1"), _ Unique:=True End Sub -- Regards, Tom Ogilvy "Bob Phillips" wrote: Sub CopyData() Columns("B:B").Insert Range("A:A").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("B1"), _ Unique:=True Columns("B:B").Copy Sheets("Sheet2").Range("A1") Columns("B:B").Delete Shift:=xlToLeft End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "erikhs" wrote in message ... Hi, I would like to make an array of unique entries from another array or column(preferably a dynamic range), and then paste back to the worksheet on a different sheet. How do i do this with the least possible code? -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=564651 |
Getting unique entries from an array/column
I certainly was. Looking at it, that seems to be correct for filtering in
Excel, I mistakenly assumed that would also apply to filtering via VBA. I'll file that away. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom Ogilvy" wrote in message ... Just to add to Bob's excellent advice: Bob appears to be under the mistaken impression that advancedfilter can't copy to another sheet. That is incorrect. It can. Use of the dummy column B is not required. Sub CopyData() Range("A:A").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Sheets("Sheet2") _ .Range("A1"), _ Unique:=True End Sub -- Regards, Tom Ogilvy "Bob Phillips" wrote: Sub CopyData() Columns("B:B").Insert Range("A:A").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("B1"), _ Unique:=True Columns("B:B").Copy Sheets("Sheet2").Range("A1") Columns("B:B").Delete Shift:=xlToLeft End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "erikhs" wrote in message ... Hi, I would like to make an array of unique entries from another array or column(preferably a dynamic range), and then paste back to the worksheet on a different sheet. How do i do this with the least possible code? -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=564651 |
Getting unique entries from an array/column
You can do the same in Excel. You just have to start at the destination cell
when you start the advanced filter and it may balk, by saying it can't determime the data, but continuing on and it works. It is cleaner in later versions, but I know for sure it works in xl97 and later and probably earlier too. -- Regards, Tom Ogilvy "Bob Phillips" wrote: I certainly was. Looking at it, that seems to be correct for filtering in Excel, I mistakenly assumed that would also apply to filtering via VBA. I'll file that away. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom Ogilvy" wrote in message ... Just to add to Bob's excellent advice: Bob appears to be under the mistaken impression that advancedfilter can't copy to another sheet. That is incorrect. It can. Use of the dummy column B is not required. Sub CopyData() Range("A:A").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Sheets("Sheet2") _ .Range("A1"), _ Unique:=True End Sub -- Regards, Tom Ogilvy "Bob Phillips" wrote: Sub CopyData() Columns("B:B").Insert Range("A:A").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("B1"), _ Unique:=True Columns("B:B").Copy Sheets("Sheet2").Range("A1") Columns("B:B").Delete Shift:=xlToLeft End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "erikhs" wrote in message ... Hi, I would like to make an array of unique entries from another array or column(preferably a dynamic range), and then paste back to the worksheet on a different sheet. How do i do this with the least possible code? -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=564651 |
Getting unique entries from an array/column
It works fine in XP. Thanks for that, I didn't know.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom Ogilvy" wrote in message ... You can do the same in Excel. You just have to start at the destination cell when you start the advanced filter and it may balk, by saying it can't determime the data, but continuing on and it works. It is cleaner in later versions, but I know for sure it works in xl97 and later and probably earlier too. -- Regards, Tom Ogilvy "Bob Phillips" wrote: I certainly was. Looking at it, that seems to be correct for filtering in Excel, I mistakenly assumed that would also apply to filtering via VBA. I'll file that away. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom Ogilvy" wrote in message ... Just to add to Bob's excellent advice: Bob appears to be under the mistaken impression that advancedfilter can't copy to another sheet. That is incorrect. It can. Use of the dummy column B is not required. Sub CopyData() Range("A:A").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Sheets("Sheet2") _ .Range("A1"), _ Unique:=True End Sub -- Regards, Tom Ogilvy "Bob Phillips" wrote: Sub CopyData() Columns("B:B").Insert Range("A:A").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("B1"), _ Unique:=True Columns("B:B").Copy Sheets("Sheet2").Range("A1") Columns("B:B").Delete Shift:=xlToLeft End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "erikhs" wrote in message ... Hi, I would like to make an array of unique entries from another array or column(preferably a dynamic range), and then paste back to the worksheet on a different sheet. How do i do this with the least possible code? -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=564651 |
| All times are GMT +1. The time now is 05:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com