Build an Array of Different Values From Column

How can I build an array with non repeating values from a column?  For 
example, in Col. A I have this:

Col. A
1
2
3
3
3
4
4

I want MyArray = Array(1,2,3,4).  No duplications.  Can I use the Split 
Function?

MyArray = Split(MyRange, "", 1, )  ' this doesn't work, Err: Type Mismatch

Thanks in Advance!
-- 
Cheers,
Ryan
0
Utf
1/26/2010 1:54:05 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1072 Views

Similar Articles

[PageSpeed] 49

Hello Ryan,

Split splits a string, not a range.

I suggest to use Lfreq or another of my UDF's I provide:
http://sulprobil.com/html/listfreq.html

Regards,
Bernd
0
Bernd
1/26/2010 3:02:44 PM
Something like this would work. It's not very pretty:

Sub arraymaker()

Dim rng As Range
Set rng = Range("A1:A13")

Dim aResult() As String
Dim i As Integer

Dim c As Range
For Each c In rng
    If WorksheetFunction.CountIf(Range("a1:" & c.Address), c) = 1 Then
        ReDim Preserve aResult(i)
        aResult(i) = c.Value
        i = i + 1
    End If
Next c

End Sub

"Ryan H" wrote:

> How can I build an array with non repeating values from a column?  For 
> example, in Col. A I have this:
> 
> Col. A
> 1
> 2
> 3
> 3
> 3
> 4
> 4
> 
> I want MyArray = Array(1,2,3,4).  No duplications.  Can I use the Split 
> Function?
> 
> MyArray = Split(MyRange, "", 1, )  ' this doesn't work, Err: Type Mismatch
> 
> Thanks in Advance!
> -- 
> Cheers,
> Ryan
0
Utf
1/26/2010 3:13:02 PM
Reply:

Similar Artilces:

Merging Multiple documents having different headers and footers ma
Dear All, I am trying to merge a several word/rtf documents into a single word documents with the below macro, the problem is now each document is having a different header and footer and some fields, Can anyone suggest me a macro code for merging multiple word documents into a single document without disturbing the headers and footers of each document? Sub MergeDocs() Dim rng As Range Dim MainDoc As Document Dim strFile As String Const strFolder = "c:\tes\" 'change to suit Set MainDoc = Documents.Add strFile = Dir$(strFolder & &quo...

Cell Values Not There ???
I'm using VBA code in Excel 2007. I am having a strange problem. The cells have content. Some of them, but not all of them, are selected from Data Validation lists (drawn from a named range). However, when I try to access their values in VBA, it says they are empty!'' For example, the simple code: Debug.Print "Cell value is: " & ActiveSheet.Cells(2, 7).Value results in the output: Cell value is: I even tried the following code, to make sure it was not a problem with the ActiveSheet reference: ThisWorkbook.Sheets("Form").Cells(2, 7)....

use a time value in a bar graph
i have values in a series like c5 9:01 d5 21:01 e5 =(d5-c5) I would like to use e5 in a bar graph it gives values which would make sense only to excel If the axis is formatted as time, it shouldn't be a problem. If it doesn't choose the scales to give clean divisions, you can choose an appropriate unit on the axis, such as 03:00. -- David Biddulph "pdfrone" <pdfrone@discussions.microsoft.com> wrote in message news:A8C14E9F-104A-464A-81B6-39DA2C679E71@microsoft.com... >i have values in a series > > like > > c5 9:01 > d5 21:01 > e5 =(d5-c5...

Re: Is there a way to HIDE a row based on a value of a cell ?
Hi Reddance, Try instead: Sub Tester() Dim rng As Range Dim i As Long If LCase(Sheets("Ctrl").Range("A1").Value) _ <> "yes" Then Exit Sub End If With Sheets("ToPrint") For i = 5 To .UsedRange.Rows.Count Step 2 If Not rng Is Nothing Then Set rng = Union(rng, .Cells(i, "A")) Else Set rng = .Cells(i, "A") End If Next i End With If Not rng Is Nothing Then rng.EntireRow.Hidden = True End If ...

MATCHING COLUMNS
I HAVE A QUESTION ABOUT MATCHING COLUMNS IN EXCEL. FOR INSTANCE I HAVE TWO COLUMNS ONE IN A AND ONE IN B WITH ALL DIFFERENT VALUES. HOW DO I MATCH COLUMN A WITH COLUMN B? AN EXAMPLE IS BELOW A B 000824108498310 000824108378310 000825252525151 000824108378310 002020204582810 000824108448310 020202222225550 000824108460310 None of your values matches any value from other column! Regards, -- AP PS: ALL CAPS means yelling: please avoid! <HOOSICK@NYCAP.RR.COM> a...

pivot table grand totals not showing for all columns...
i have a pivot table that shows the grand total for some columns, but not for others... there are no differences in the data layout, yet this happens? any clues as to why? and how I can get the grand totals to show for all fields. thanx in the PivotTable floating toolbar, click PviotTable, then Table Options, select 'Grand totals for columns' option. ...

How do I format a field based on the value
I have a form that is populated from a query using several tables. In the detail section I have the following fields: Category Code qty PO number ... .... ... The qty is populated in the query based on whether or not a PO number is available. If the PO number is not available then it comes from table a, if it is available then it comes from table b. On the form, I have my fields color-coded for easy reading. I would like to be able to color the border and column heading based on where this value comes from. If table A, then it is blue, if table b then it...

how to find all matches in an array
I have a task list that in one column lists the person primarily responsible for it and in the 2nd column is the back up person. The 3rd column is the task. On another sheet I want to put in someones name and bring up all tasks they are responsible for and all the tasks they are backup for. What is an easy way to do this. Easiest way IMHO would be to use advanced filter http://www.contextures.com/xladvfilter01.html -- Regards, Peo Sjoblom (No private emails please) "Greg" <Greg@discussions.microsoft.com> wrote in message news:FA4CB596-6451-4569-81AF-24EC18C50180...

How do I arrange entries in a column alphabetically
I have typed a list of entries in a column in Excel 2003 and now I want to list them alphabetically. Any ideas how I do this? Select the column, go to Data > Sort, and sort ascending. HTH Jason Atlanta, GA >-----Original Message----- >I have typed a list of entries in a column in Excel 2003 and now I want to >list them alphabetically. Any ideas how I do this? >. > ...

grouping cells to sort by only one column
Hi, New to this so please bear with me. I have a spread sheet where I am listing names in column A and othe info relating to that name in columns B and C in the two rows below th name. I then start a different name in the next row down with the info in t two rows below that, and so forth. I want to be able to add more names in the future and be able to sor all of this by column A only. The problem I am having is the info i the other columns moves. I need it to somehow attatch and stay put wit it's corisponding name without moving. In other words how do I make everything from A1 to ...

Excel's column width format box
Does anyone know why I am unable to enter a number into Excel's column width box, yet using the mouse, I am able to adjust the width. When opening the text box, I can delete the number that appears, but am unable to even reenter that one after it is deleted. I must tell you that I'm a new user who's working through numerous tutorials. Thanks, Hi do you get an error message or what happens exactly -- Regards Frank Kabel Frankfurt, Germany rly2rys wrote: > Does anyone know why I am unable to enter a number into Excel's > column width box, yet using the mouse, I am ...

Max Value
I am trying to use Conditional Formatting to find the Max value in a Row using =Max(B6:AC6), having that cell Highlight to a different color but the =Max is highlighting them all. Help phuser wrote: >I am trying to use Conditional Formatting to find the Max value in a Row >using =Max(B6:AC6), having that cell Highlight to a different color but the >=Max is highlighting them all. > There may be other ways, but what I do is create a column, or in your case cell, (can be hidden, or way out of sight) having the formula =Max(B6:AC6). Then use the value of that cell as the co...

Xpath with multiple values..
Is is possible to use XPath with multiple values like in SQL Queries (AND Clause).For example: If I have xml: <rows> <row code=1/> <row code=2/> <row code=3/> <row code=5/> <row code=6/> <row code=7/> <row code=100/> </rows> Now I want only to select rows 1,2,3 an 100 XPath("rows/row[@code='1' and @code='2' and @code='3' or @code='100']"). I tryed like this above, but I got nothing. Any examples. Regards. Try using ORs all the way. An AND situation is impossible here...

How to fill cell with two combobox values?
I am looking for VB code for command button that fills two combo box values and a toggle value (Y/N) behind each other in one cell. Bart Excel 2003 ...

How to clean 0x0E value from file when using XSL?
I have some code that cleans up an xml file before running xsl transformation on it due to what I call bad characters in the data. I have as an example: str = Replace(str, "&#x19;", ".") str = Replace(str, "&#x18;", ".") In there as filters to strip out characters XSL doesn't like and replace it with periods. I am now getting an exception of a hex value of 0x0E however can't figure out what the string replace value should be for this one. From what I can tell, it's some kind of "shift out" character whatever that is. A...

How do I assign a set of values to a selection from a drop list?
Hi. I am trying to assign a set of values, in separate fields ,to a each selection from a drop-down list in Excel (using data validation). What I am trying to do is very smilar to, say, to selecting a SKU from a list and having the product description, unit price etc. fill into their corresponding fields automatically. For example, I pick SKU "11111" from a drop-down list to fill the SKU field, then, automatically, "5-inch widget" comes up in the product description field and "$5.00" shows up in the unit price field. Please help. Mike Along with the d...

Report Columns
I am trying to add columns to my report but am having some difficulty. The report is basically a form letter. It is grouped on the employee’s unique ID. In that group header is the letter, Dear so and so etc. It is the details portion that I am trying to get into two columns. Is there something I am missing on how to do this. I would rather no put a sub report in to accomplish this. Thank you Jason, Well, the subform would be an easy way to go about that. However, I would think you could use the ID group header to display the first part of the letter, the detail section to disp...

Formula involving different sheets
Hi Using Excel 2003. In sheet 1, cell A1: =200/1200 In sheet 2, cell B2: =300/1200 In sheet 3 in a cell, I want to do =200/1200 + 300/1200 by cell reference. How do I do so? Thanks. Your formula would be: =sheet1!a1+sheet2!b2 The best way to create these formulae is to get Excel to do it. 1: Type = into your cell 2: Navigate to your sheet1!a1 cell. You will notice in the formula bar that Excel is adding its address to your formula 3: Type + 4: Navigate to your sheet2!b2 cell. 5: Hit enter Once you learn this, you will never type a cell address again. Regar...

Final entry in array
If I have an array (B2:B170), what formula do I need to get the final figure in the list reading from top to bottom? It could be cell 125, or cell 37, it will vary. On Nov 17, 4:41=A0pm, Esradekan <esrade...@gmail.com> wrote: > If I have an array (B2:B170), what formula do I need to get the final > figure in the list reading from top to bottom? =A0It could be cell 125, > or cell 37, it will vary. Forgot to say TIA. Sorry, stressed. Esra Hi, Try this =LOOKUP(TRUE,ISNUMBER(1/B2:B170),B2:B170) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashish...

Q: How different colum-widths?
Hello NG, i am working on a word document, wich contains some excel sheets as inserted objects. now i want to merge some of these excelsheets. unfortunately they have different coloum- widths. is it possible to merge them and say to ecxel, that from here and further down to use different coloum- widths, then in the upper part of the sheet? thanx, Tom. -- _______________________ http://tom.lautenbacher.biz _______________________ Excel doesn't support different columnwidths per row. You might be able simulate it by merging cells together (format|cells|alignment tab). But merging ...

Toolbar of different sizes of button
Hi All, I am trying to create a toolbar for IE using ATL+MFC. I want to create a toolbar just like google but without any combobox etc. simple buttons but I have to have different size of buttons on the toolbar. like the first button would be more in width the second and thrid one would be smaller. I tried using SetBitmapSize(); SetButtonSize(); for this but it doesnt seem working.(I am not using imagelist at the moment) if it is better to use imagelist in this situation? more over I put a button image extracting it from MSN toolbar it was a bitmap with pink back ground when I used it as im...

Checking Version/Build of CRM client
The current full version build of the V3C indicates its 3.5300.0.1361. When you check the version in the Outlook client, it displays the first few sections but leaves off the 1361. I'm look for a way to verify if the V3C has in fact been applied to an Outlook client. -- Thank You, Robert Harrison Portland: 503-345-9176 ext 814 Seattle: 206-686-3254 ext 814 Gateway Solutions Inc www.GSIcrm.com Certified Microsoft CRM Solution Partner ...

Copy column headings AND formula totals to blank spreadsheet
Hi Everyone! I apologize that this was likely asked before but could someone show me how I can copy my 'column headings, column widths and totals formula' to a blank spreadsheet tab? I have set up budget spreadsheet where I input my receipts to keep track of monthly expenditures. Could someone explain how to copy the column headings, the column widths, AND the formulas of each column to a blank spreadsheet tab so each month will contain the 'same headings, same column widths and the same formula for each column'? Thanks so much for any help!!! katy Assuming you alr...

finding 600 emails in a column
Hi! Thank you for taking the time to read this. I run an internet retail business. We advertise on search engine large and small. I have to make a decision on whether or not one of our smaller ad site is paying off. Basically, the ad site supplies us with a list o POTENTIAL CUSTOMER emails. We send out monthly promotional emails t them. Now i need to track these emails to see if any of them have becom ESTABLISHED CUSTOMERS. So far, I fed information from our main data base of ESTABLISHE CUSTOMERS into EXCELL so all the ESTABLISHED CUSTOMER emails are in on column. But i don't kn...

Column that needs separation
I am very new with excel and I am putting together a worksheet that has a column consisting of two primary names. I would like to format the entire column with the result being both names having their own color throughout the column. Alternatively, I could go to each row and highlight the name but there are about 1000 rows. Thanks, Aaron Say column A has entries like: James Ravenswood Run this small macro: Sub Colorizer() Dim A As Range, r As Range Set A = Intersect(ActiveSheet.UsedRange, Range("A:A")) For Each r In A v = r.Value s = Split(v, ...