range of unique cells

Hi All,

If I make a unique list with advanced filter, how can I define the range 
consisting of the unique cell, e.g. if in Range("A8:A23") cells A8, A9, A11, 
A15, A16 remained visible after fitering then what is the statement to define 
a range consisting A8, A9, A11, A15, A16 cells?


Range("A8:A23").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
set uniqcells = ???

-- 
Thanks!
Stefi

0
Utf
2/16/2010 9:23:01 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
757 Views

Similar Articles

[PageSpeed] 31

Set uniqcells = Range("A8:A23").SpecialCells(xlCellTypeVisible)

HTH

Bob

"Stefi" <Stefi@discussions.microsoft.com> wrote in message 
news:FABF37BF-4B40-405D-8965-DA9D54966DA9@microsoft.com...
> Hi All,
>
> If I make a unique list with advanced filter, how can I define the range
> consisting of the unique cell, e.g. if in Range("A8:A23") cells A8, A9, 
> A11,
> A15, A16 remained visible after fitering then what is the statement to 
> define
> a range consisting A8, A9, A11, A15, A16 cells?
>
>
> Range("A8:A23").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
> set uniqcells = ???
>
> -- 
> Thanks!
> Stefi
> 


0
Bob
2/16/2010 9:34:10 PM
Hi

Set uniqcells = Range("A8:A23").SpecialCells(xlCellTypeVisible)

Just remeber that when using a filter, excel expect A8 (first row) to be a 
header, and not a part of the filtered values.

Regards,
Per

"Stefi" <Stefi@discussions.microsoft.com> skrev i meddelelsen 
news:FABF37BF-4B40-405D-8965-DA9D54966DA9@microsoft.com...
> Hi All,
>
> If I make a unique list with advanced filter, how can I define the range
> consisting of the unique cell, e.g. if in Range("A8:A23") cells A8, A9, 
> A11,
> A15, A16 remained visible after fitering then what is the statement to 
> define
> a range consisting A8, A9, A11, A15, A16 cells?
>
>
> Range("A8:A23").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
> set uniqcells = ???
>
> -- 
> Thanks!
> Stefi
> 
0
Per
2/16/2010 9:36:17 PM
On Feb 16, 4:23=A0pm, Stefi <St...@discussions.microsoft.com> wrote:
> Hi All,
>
> If I make a unique list with advanced filter, how can I define the range
> consisting of the unique cell, e.g. if in Range("A8:A23") cells A8, A9, A=
11,
> A15, A16 remained visible after fitering then what is the statement to de=
fine
> a range consisting A8, A9, A11, A15, A16 cells?
>
> Range("A8:A23").AdvancedFilter Action:=3DxlFilterInPlace, Unique:=3DTrue
> set uniqcells =3D ???
>
> --
> Thanks!
> Stefi

untried, but perhaps:

Range("A8:A23").AdvancedFilter Action:=3DxlFilterInPlace, Unique:=3DTrue
Set uniqcells =3D range("A8:A23").SpecialCells(xlCellTypeVisible)
0
Jef
2/16/2010 10:09:03 PM
Thanks to all of you, it worked.

I mislead myself when I tried this solution by myself and wanted to check 
the result with uniqcells.rows.count which gave 3 and it was wrong. Later I 
found out that uniqcells.count gives the right result, while 
uniqcells.rows.count gives the number of rows in the first contiguous part of 
uniqcells, it's really 3, but I still don't understand the reason of 
differing the number of rows and the number of cells in a one column wide 
range. Could you explain it?

Thanks!
Stefi



„Bob Phillips” ezt írta:

> Set uniqcells = Range("A8:A23").SpecialCells(xlCellTypeVisible)
> 
> HTH
> 
> Bob
> 
> "Stefi" <Stefi@discussions.microsoft.com> wrote in message 
> news:FABF37BF-4B40-405D-8965-DA9D54966DA9@microsoft.com...
> > Hi All,
> >
> > If I make a unique list with advanced filter, how can I define the range
> > consisting of the unique cell, e.g. if in Range("A8:A23") cells A8, A9, 
> > A11,
> > A15, A16 remained visible after fitering then what is the statement to 
> > define
> > a range consisting A8, A9, A11, A15, A16 cells?
> >
> >
> > Range("A8:A23").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
> > set uniqcells = ???
> >
> > -- 
> > Thanks!
> > Stefi
> > 
> 
> 
> .
> 
0
Utf
2/17/2010 9:25:01 AM
In a one column range they should be the same. Are you sure your range was 
just one column wide?

HTH

Bob

"Stefi" <Stefi@discussions.microsoft.com> wrote in message 
news:DCD236ED-04EC-49C1-B4B2-137CD29114FA@microsoft.com...
> Thanks to all of you, it worked.
>
> I mislead myself when I tried this solution by myself and wanted to check
> the result with uniqcells.rows.count which gave 3 and it was wrong. Later 
> I
> found out that uniqcells.count gives the right result, while
> uniqcells.rows.count gives the number of rows in the first contiguous part 
> of
> uniqcells, it's really 3, but I still don't understand the reason of
> differing the number of rows and the number of cells in a one column wide
> range. Could you explain it?
>
> Thanks!
> Stefi
>
>
>
> "Bob Phillips" ezt �rta:
>
>> Set uniqcells = Range("A8:A23").SpecialCells(xlCellTypeVisible)
>>
>> HTH
>>
>> Bob
>>
>> "Stefi" <Stefi@discussions.microsoft.com> wrote in message
>> news:FABF37BF-4B40-405D-8965-DA9D54966DA9@microsoft.com...
>> > Hi All,
>> >
>> > If I make a unique list with advanced filter, how can I define the 
>> > range
>> > consisting of the unique cell, e.g. if in Range("A8:A23") cells A8, A9,
>> > A11,
>> > A15, A16 remained visible after fitering then what is the statement to
>> > define
>> > a range consisting A8, A9, A11, A15, A16 cells?
>> >
>> >
>> > Range("A8:A23").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
>> > set uniqcells = ???
>> >
>> > -- 
>> > Thanks!
>> > Stefi
>> >
>>
>>
>> .
>> 


0
Bob
2/17/2010 3:03:34 PM
Hi Bob,

I checked again: the range was definitely one column wide. 

uniqcells.rows.count gave 3 while uniqcells.count gave 8 which was right.

When I increased the width to 2 for testing purposes, uniqcells.rows.count 
gave still 3 while uniqcells.count gave 2*8=16 which was also right.

-- 
Regards!
Stefi



„Bob Phillips” ezt írta:

> In a one column range they should be the same. Are you sure your range was 
> just one column wide?
> 
> HTH
> 
> Bob
> 
> "Stefi" <Stefi@discussions.microsoft.com> wrote in message 
> news:DCD236ED-04EC-49C1-B4B2-137CD29114FA@microsoft.com...
> > Thanks to all of you, it worked.
> >
> > I mislead myself when I tried this solution by myself and wanted to check
> > the result with uniqcells.rows.count which gave 3 and it was wrong. Later 
> > I
> > found out that uniqcells.count gives the right result, while
> > uniqcells.rows.count gives the number of rows in the first contiguous part 
> > of
> > uniqcells, it's really 3, but I still don't understand the reason of
> > differing the number of rows and the number of cells in a one column wide
> > range. Could you explain it?
> >
> > Thanks!
> > Stefi
> >
> >
> >
> > "Bob Phillips" ezt írta:
> >
> >> Set uniqcells = Range("A8:A23").SpecialCells(xlCellTypeVisible)
> >>
> >> HTH
> >>
> >> Bob
> >>
> >> "Stefi" <Stefi@discussions.microsoft.com> wrote in message
> >> news:FABF37BF-4B40-405D-8965-DA9D54966DA9@microsoft.com...
> >> > Hi All,
> >> >
> >> > If I make a unique list with advanced filter, how can I define the 
> >> > range
> >> > consisting of the unique cell, e.g. if in Range("A8:A23") cells A8, A9,
> >> > A11,
> >> > A15, A16 remained visible after fitering then what is the statement to
> >> > define
> >> > a range consisting A8, A9, A11, A15, A16 cells?
> >> >
> >> >
> >> > Range("A8:A23").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
> >> > set uniqcells = ???
> >> >
> >> > -- 
> >> > Thanks!
> >> > Stefi
> >> >
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
2/25/2010 12:04:01 PM
Reply:

Similar Artilces:

Colors for Text and Cell Background
I an running Excel 2007 (part of Office 2007) on my Win/XP Workstation. For both cell background and text colors, the new menu system has "Theme Colors" and "Standard Colors". I know I can also choose "More Colors" to get virtually anything I want. However, there are some colors that I really like and use a lot. I would like to change the Standard Color offerings from what Microsoft has chosen to something more to my liking. Can anyone tell me if this is possible? If it is, would you please let me know how to do it? Thanks, David To apply a color other t...

Is there Formula to Tab to certain cells
Is there a formula to tab through certain cells? I have a form with about 15 cells that I would like to access easily through the tab key....is there any way to do that? One way to explicitly control the exact "next cell of focus" selection, is to select the cells in the desired order of travel, and then preserve this ordered movement by creating a named range. This old post describes the steps that can be taken to create such a "named range". http://tinyurl.com/39vzv -- HTH, RD ============================================== Please keep all correspondence within ...

Copy whole table into one cell
Office Excel 2003 on Windows XP I'm trying to create a report-maker with excel... ugh! Anyways, there is a template that users are filling out saying whether something is defected or not... that's not important, the important part is, that I'm having VBA go though the table (using a button after it is filled out) and deleting the rows that aren't defected... in other words, if there are blank cells in column C (for example), the whole row in which that blank cell is located is deleted. I'll never have more than 40 rows and 5 columns in the table so I don'...

Extract different strings from a cell?
If I have "100BDDDABAABD" in a cell and I'd like to extract the last 10 characters and put each character into a different cells. Then, the first rest of characters (first 3 in this case) into another cell. How do I do that in Excel? Thanks, Since you said "first 3 in this case", I'll assume that the number of characters will vary in each cell. With data in A1, enter this in B1, and copy across to K1: =MID(RIGHT($A1,10),COLUMNS($A:A),1) Then, in L1, enter this for the remaining characters: =LEFT(A1,LEN(A1)-10) -- HTH, RD -----------------------------------...

resize control checkbox with cell
First question: One of the tab in my workbook has several checkboxes arranged in a number of columns. One of the set is over the cells, say, A1:A20. I would like to attach these checkboxes to the cell they belong to so that they will have exactly the same size with their cells, and resized along with the cells. Is this possible? Second question: How much overhead does control checkbox has over simply using cell.value = "Y" or "N" ? I just saw an answer to another post concerning toggling a cell between "" and "X". If this works, I could remove ...

Force spaces in a cell (Excel 2003)
Hi all, I need to force a specified number of spaces in a cell so, when the file is saved in .CSV format, results a delimited and spaced file. For example, something like this: testCellA1 ;testCellB1 ; etc How can I obtain this? Thanks in advance. -- Luigi http://blogs.dotnethell.it/ciupaz/ You can do it with the REPT function - suppose you want the field to be 20 characters wide: =3DA1&REPT(20-LEN(A1)," ") Hope this helps. Pete On Aug 1, 8:09=A0am, Luigi <ciupazNoSpamGra...@inwind.it> wrote: > Hi all, > I need to force a specified number of s...

Insert Text From Different Cell
I would like to enter a company name, such as "ABC Co" in a cell o sheet1 and then on sheet2 have a cell with text "Proposal for ABC Co". I would also like to be wildly rich. Can anyone help with either o those -- Jorad ----------------------------------------------------------------------- Joradi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2686 View this thread: http://www.excelforum.com/showthread.php?threadid=40105 Lets say you manually enter in Sheet1, cell A1 ABC Co In sheet2, cell A1, enter ="Proposal for " & Sheet1...

Need a method to convert varying french words to english within a cell
I have a spreadsheet with descriptive terms in French. I would like a search and replace method that can replace multiple the target words within a cell, when that cell contains additional words and names that are not to be altered. How do I do this with substitute and a vlookup from a table of target words and english equivalents or is there a VBA macro that will do this? Thank you for your time and any help you can give. Regards G Have you tried Edit > Replace........ Vaya con Dios, Chuck, CABGx3 "hollerg@basf.com" wrote: > I have a spreadsheet with descriptive te...

if cell a1 is greater than 0, how can i make b1's value go to zero?
if cell a1 is greater than 0, how can i make b1's value go to zero if value is entered in b1 -- Message posted from http://www.ExcelForum.com Hi A cell can contain either a formula, like =IF(A1>0,0,501) , or an entered constant value. So this task (enter and then calculate within the same cell) needs a macro solution, unless you can use one of the 16 777 214 remaining cells on the worksheet to produce the result in question for you. -- HTH. Best wishes Harald Followup to newsgroup only please. "froggydetail" <froggydetail.y8kjy@excelforum-nospam.com> wrote in mess...

Updating a cell?
Here is what I need to do: For each cell in a worksheet I have a dropdown list of minutes whic can be picked from. I then want to go back to a specific cell and pic another value which will be added to the value that was originall entered for that cell. So for cell A1 say, I first select 10 and the later go back to cell A1 and select 20 - I want the cells new value t be 30. Is there anyway I can do this -- Message posted from http://www.ExcelForum.com Hi this could only be done with VBA using an event procedure. See http://www.mcgimpsey.com/excel/accumulator.html for an example to do this ...

Go to next cell if full 01-16-10
Hi everyone, I need help if possible: In cell 'A1' I would like to enter data I would like to transfer this data to a different cell lets say its cell 'D1' buy 'Cut & Paste'. Is it possible to have a macro to identify that cell 'D1' contains data so next entry will be pasted in cell 'D2' and next in cell 'D3' and so on. Thank you for your kind support. Albert Try the Event-Macro in the sheets level: =========================================== Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEv...

Query Date Range
How can I use Between/And to prompt for date range that includes the start and end date? Hi, Design a select query for your table. add all fields to the design grid. In criteria box of the date field write this Between #BeginningDate# And #EndingDate# and run the query to find results. JB "karenfocus" wrote: > How can I use Between/And to prompt for date range that includes the start > and end date? My current criteria is in a query date field and is written Between [Enter start date] And [Enter end date] but when I run the query from the first day of the month to the...

Displaying sheet and cell value in another cell
I am using the Min formula to find the minimum value from tables on seperate worksheets. The summary sheet displays the minimum value. Is there a way to indicate on the summary sheet, from which sheet that the min value was selected from ? -- MB Hi using an approach from Harlan Grove try the following: 1. put the names of all your sheets in a range of your summary page. e.g. in the cells X1:X10 2. In A1 put your MIN formula (I assume your values are in A1:A10 on all sheets) 3. Now use the following array formula (entered with CTRL+SHIFT+ENTER) =INDEX(X1:X10,MATCH(TRUE,COUNTIF(INDIRECT(&qu...

Convert range of numbers to letter
Hello. I hope this makes sense. I have a spreadsheet that has columns with numbers ranging from 1 to 100. I would like to convert them to letters i.e. 90 - 100 = A 80 - 90 = B 70 - 80 = C 60 - 70 = D and so on Is this possible? Thank you JB "JB" <badspam@awyway.com> wrote in message news:OUlxDBVPGHA.3728@tk2msftngp13.phx.gbl... > Hello. > I hope this makes sense. > I have a spreadsheet that has columns with numbers ranging from 1 to 100. > I would like to convert them to letters > i.e. 90 - 100 = A > 80 - 90 = B > 70 - 80 = C ...

How to set a value in a cell whose address is held in another cell
Say I have A1=4 and A2="D3", can I write a macro to set D3=4, using the information in A1:A2? -- Ellis Morgan Sub MakeItReal() Dim strText As String strText = Range("A2").Text Range(strText).Value = Range("A1").Value End Sub -- Jim Cone Portland, Oregon USA . http://www.mediafire.com/PrimitiveSoftware . (Data Options add-in: row stuff/date picker/random stuff) "Ellis Morgan" <ellis@nospam.demon.co.uk> wrote in message news:HYPSVWGORzfPFwv1@mrtlfrm.demon.co.uk... > Say I have A1=4 and A2="D3", can I write a macro to se...

cell color change
Can the background color of a cell be changed through programming? I have a customer chart which includes a cell where the date of a payment is shown. I would like to have the color of the cell change to red as an alert if a payment has not been made within a set period of time. Thanks for any help you can provide to this self-taught absolute beginner in Excel. David --- Message posted from http://www.ExcelForum.com/ Take a look at Conditional Formatting in XL Help. No programming required. In article <David.H.12gaw6@excelforum-nospam.com>, David H <<David.H.12gaw6@ex...

Dynamic Offsetting, Names, Arrays, Range, Chart Values. NEED HELP
Hi, I would like to enter values into a Chart from a Named Array(Range). The Array should not be an actual Range, but a virtual array based on logic applied to a Range. In my case picking values in ColumnB based on values in ColumnA Ideally I woud like to write something like: Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1) and then make a Chart out of it. (I actually want to do something more complex: a variable-width bar chart using an X/Y scatter, w/o using Bullen's method) My problem is that ONE offset is applied on the whole array, and not DYNAMICALLY to the different items. Need...

Formatting Excel cell through Javascript
I want to export the data from my HTML page to Excel file and I want my data should be formatted whose format will be like (234.45) and the color should be red. i want to apply this formatting in javascript itself. Can anyone knows how to do it? ...

Changing Year in Date Range
I am using a crosstab query for selecting data for a date range spanning 12 months over two different years using a "Between & And" date range e.g Between #01/04/07# And #31/03/08# For the following 12 month period I can manually change the Year numbers in the above criteria. my question is: is it possible to change or select the Year number without having to change it manually in the query design view by say a parameter box? Have you considered using this --- Between CVDate("4/1/"&Format(Date(),"yyyy")) And CVDate("3/31/"&Format(Da...

Reference to Cell in Other WSheet in WBook
We develop conceptual cost estimates for schematic design of buildings. Frequently we have multiple worksheets (Estimate, Summary, Area, etc.) that have a common header of five fields. I'd like all but one to get their values from one authoritative worksheet, but when I enter the cell reference (by pointing to it), it inserts the cell reference rather than the value, as in: =Estimate!B1:B1 There must be a parameter set somewhere but I can't find it. Anyone know how? Thanks. Kevin Sprinkel Becker & Frondorf Make sure the cell your formula's in is not formatted as ...

selecting cells #4
When holding down ctrl to select a number of cells to format them or see the sum in the bottom of the screen - and you accidentally select one you dont want - is there a way to unselect that one without starting over ? Thanks, Yosef Take a look at this: http://www.cpearson.com/excel/unselect.htm there's no builtin way to do it, other than starting over... -- Regards, Juan Pablo Gonz�lez Excel MVP "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:9EA0DDD8-4668-430B-BB5E-C15549FC9346@microsoft.com... > When holding down ctrl to select a number ...

format cell type
Hi everyone, in a spreadsheet, I have a complex data type that I want to sort correctly. The format contains a prefix that contains both letters(A) and numbers(0) followed by a suffix after a hyphen and another number. Example: 000A0000-1 and 000A0000-10 The thing Excel is messing up in, is if the complete prefix (000A0000) is the same, but a different number after the hyphen, it sorts it 1, 10, 11, 12, 13, ..., 2, 20, 21, 22, ... 3, etc. I was wondering if anyone knew how to set up a custom format type that will allow a suffix stored as text and the suffix after the hyphen stored as a n...

VLookUp and DropDown List in the same cell
Hi everyone, I know that this is possible but I'm not sure how to accomplish this. In column E, I have a list of zip code. In column I, I have the "Priority" category. Priority will be based on the zip code in column E. This will be accomplished by VLookUp formula, where I will have a separate sheet store all the zip codes and the priorities (ie. Priority #1, Priority #2 and so on). The challenge part is that column I "Priority" column needs to be able to use as a Drop Down List as well, in case that the VLookUp formula needs to be overridden for s...

Sorting cells #2
When you try to sort certain data by selecting the cells, but leaving adjacent cells unselected, you get a "sort warning" asking if you would like to expand the selection. How do you stop this from happening? Joey If you are selecting cells in one column or row only you will get this message. I have never found a way to prevent it. Hopefully we both can learn something here. Gord Dibben Excel MVP On Mon, 24 Jan 2005 13:39:05 -0800, "JoeyJoeJoe" <JoeyJoeJoe@discussions.microsoft.com> wrote: >When you try to sort certain data by selecting the cells, but lea...

Pivot Table Data Source, point to cell?
Is there any way to point the data source block to a cell. I want to simply update a cell (i.e. A1) with the range instead having to open the pivot table and changing from the wizard. Is there a way to do this? Example: Cell A1 contains: '[Output 3.24.xls]FORMALS-OUT'!$A$5:$P$65536 Data source in pivot table points to cell A1 to get the range. please tell me there's a way to do this.... thanx.! hi, Jeremy ! > Is there any way to point the data source block to a cell. > I want to simply update a cell (i.e. A1) with the range instead having to open the pivot table >...