advanced filter question

Hello all again,

I've got some data in column A, sheet3 and I have the following code (that 
works without problem)

Sub FilterUniqueItems()

Dim i As Integer
Dim list As Variant
Dim DataRange As Range

Set DataRange = Worksheets("sheet3").Range("A1:A10")

list = DataRange.AdvancedFilter(Action:=xlFilterCopy, 
Copytorange:=Worksheets("sheet3").Range("B1"), Unique:=True)

End Sub


Is it possible to determine (programatically, I mean) how many and what 
values the advancedfilter extract? I'd like to treat the data before pasting 
it into Excel.

Best regards. 


0
jkanoj (5)
6/8/2006 9:46:46 PM
excel 39879 articles. 2 followers. Follow

2 Replies
195 Views

Similar Articles

[PageSpeed] 23

After the filtered data is in column B, you could use:

with worksheets("sheet3")
  msgbox .range("b2",.cells(.rows.count,"B").end(xlup)).cells.count
  'or since you plopped the data into row 1
  msgbox .cells("B1").end(xldown).row - 1
end with

K7 wrote:
> 
> Hello all again,
> 
> I've got some data in column A, sheet3 and I have the following code (that
> works without problem)
> 
> Sub FilterUniqueItems()
> 
> Dim i As Integer
> Dim list As Variant
> Dim DataRange As Range
> 
> Set DataRange = Worksheets("sheet3").Range("A1:A10")
> 
> list = DataRange.AdvancedFilter(Action:=xlFilterCopy,
> Copytorange:=Worksheets("sheet3").Range("B1"), Unique:=True)
> 
> End Sub
> 
> Is it possible to determine (programatically, I mean) how many and what
> values the advancedfilter extract? I'd like to treat the data before pasting
> it into Excel.
> 
> Best regards.

-- 

Dave Peterson
0
petersod (12004)
6/8/2006 10:02:11 PM
Thanks a lot Dave!!!

Best regards


"Dave Peterson" <petersod@verizonXSPAM.net> escribi� en el mensaje 
news:44889E63.C7288162@verizonXSPAM.net...
> After the filtered data is in column B, you could use:
>
> with worksheets("sheet3")
>  msgbox .range("b2",.cells(.rows.count,"B").end(xlup)).cells.count
>  'or since you plopped the data into row 1
>  msgbox .cells("B1").end(xldown).row - 1
> end with
>
> K7 wrote:
>>
>> Hello all again,
>>
>> I've got some data in column A, sheet3 and I have the following code 
>> (that
>> works without problem)
>>
>> Sub FilterUniqueItems()
>>
>> Dim i As Integer
>> Dim list As Variant
>> Dim DataRange As Range
>>
>> Set DataRange = Worksheets("sheet3").Range("A1:A10")
>>
>> list = DataRange.AdvancedFilter(Action:=xlFilterCopy,
>> Copytorange:=Worksheets("sheet3").Range("B1"), Unique:=True)
>>
>> End Sub
>>
>> Is it possible to determine (programatically, I mean) how many and what
>> values the advancedfilter extract? I'd like to treat the data before 
>> pasting
>> it into Excel.
>>
>> Best regards.
>
> -- 
>
> Dave Peterson 


0
jkanoj (5)
6/9/2006 2:24:22 PM
Reply:

Similar Artilces:

Reports Question
I am interested in generating a report that will show me my top customers by category over a given period of time. I would like this ranked by the customers total sales over that period. Is this possible? Thanks! ...

CRM 3 Database Development question
Hi guys, Thanks in advance for any help. I guess, the question relates very much in how I can, in CRM3 to map the campaign response entity to the lead entity, so that I can transport custom fields from one to the other. Problem is, I am not sure how should I do this! I was thinking of creating a custom field on the lead entity and add a Nto1 relationship from the primary key in the campaign response entity to the custom field in the lead entity. But I am not very sure it will work. Anyone has attempted something of the sort? Thanks again, ...

Budget question....
Does anyone know of a way to not include "Transfers out of budget accounts" in my budget? I know of a manual way to do it. In the Transaction Register you can “right click” on the transaction and “exclude from budget”. In fact, that goes for any type transaction that you want to exclude from the budget report. "shaunx4" wrote: > Does anyone know of a way to not include > "Transfers out of budget accounts" in my budget? > Set all the target accounts of those transfers to be included in the budget in their account details? "shaunx4" &l...

Distribution List Questions
Exchange 2003 SP1, Native Mode. I'm pretty new to Exchange, so forgive any dumb questions I may ask. I'd like to create a distribution list that consists of certain members who will always be on the list, plus some other distribution lists. Specifically, some query-based lists. Is this possible? I've played around with it, but haven't had any luck. I can't seem to make a QBDL a member of another (regular) distribution list. QBDL's look really cool, but so far I've not had much success making them do what I want. This is probably mostly due to my own lack of knowl...

Paste special question
Is there a know way to paste a chart into a new workbook as a jpeg/pn using paste special if the chart was created with autofiltered data Currently, paste special cannot be selected from the pulldown menu o charts created/updated with autofiltered data. THX G.Shar -- Message posted from http://www.ExcelForum.com Here are a couple that can export a chart to a file Sub ExportChartGIF() ActiveChart.Export Filename:="C:\a\MyChart.gif", _ FilterName:="GIF" End Sub Sub ExportChartJPG() ActiveChart.Export Filename:="C:\a\MyChart.jpg", _ FilterName:="jpeg&...

Priv.edb & Exchange 5.5 SP3
Hi: I have several questions on Priv.edb that I would really appreciate some help with. The server is Exchange 5.5 SP3 and NAV 1. The Priv.edb has been sitting at 13.8G for some time now. The amount of mail traffic and users has really not changed. Starting last night the Priv.edb went to the 16G limit. I archived off several users, ESEUTIL /d the Priv.edb back down to 13.9G at 3am. At 9am the Priv.edb was back up to 15.6G. I can watch the Priv.edb grow in size at a rate of 8,190KB every 5 mins. Any ideas on what is causing this? 2. If I add up all the users "Total KB" in the ...

Pivot Chart question Access 2007
I am expanding an Access 2007 db for maintenance agreement customers in my heating and air company. One of the charts i am trying to capture is the number of 1st time visits by quarter going back to the 1st qtr of 2006. This is also referred to as the number of gross adds. For some reason, i can create the chart and drill down to the quarter, however everytime i close the darn thing, i lose all of the data. When the chart is re-opened the data is gone. I have other pivot charts out there that come right up each time. But for some reason, this one doesnt. I've done this like 5 times...

Search not filter and display results in subform.
Hi, I have a small split DB BE and FE. I need to implement a search form where the results shows in a subform. I tried this http://allenbrowne.com/ser-62.html which is good but because there are about 2000 records and the BE is in a share drive it takes a while for the subform to load to show all records before I can filter. what I need is a "on the fly" filtering or search. I think I have to use SQL and build a query on the fly or is there a faster way? any ideas will be a appreciated. thanks! ...

Mail Merge Question for Office 2003
We have a table in XLS that is merging with a Word document. The XLS shows all the number with only 2 decimal places x.yy but the merge show a whole bunch of other 000000000 after the last digit. How do I get the merge to show just what is in XLS? Ex. It should just pull over x.yy but it's pulling over x.yy00000000000 Thanks! Debra Dalgleish posted this to a similar question about formatting dates/decimals. There's an article on the Microsoft web site that might help you: Answer Box: Numbers don't merge right in Word http://office.microsoft.com/en-ca/assistance/HA0...

A question about thread,please help me
My program environment is .net2005.The partial code is shown as below: void CfinalView::OnRButtonDblClk(UINT nFlags, CPoint point) { CRect rc; GetClientRect(&rc); if(point.x>rc.left&&point.x<(rc.right/2)&&point.y>rc.top&&point.y<rc.bottom) { //AfxBeginThread(RUNTIME_CLASS(CDetailPlot)); } if(point.x>(rc.right/2)&&point.x<rc.right&&point.y>rc.top&&point.y<(rc.bottom/2)) { //AfxMessageBox(_T("haha")); AfxBeginThread(RUNTIME_C...

Costumize date condition operators in a filter
I am creating a view "Anniversary next month" that should return all contacts that in the field Anniversary, the month is the next month (December). e.g. (dd/mm/aaaa) "01/12/1987", "31/12/1969" and "10/12/2000" are values that fit the condition. If there is a way to costumize the date operator in a filter, maybe we can add an opperator like "Same as Next Month" that returns all records whose month is the same as the next month. e.g. if next month = December this dates will be returned: dd-mm-aaaa 15-12-1987, 31-12-1976, 01-12-2004. Do...

Question about comparing text columns in Excel?
I am trying to figure out how to create a Vlookup formula that would do the following: I want to find the names in Column D that are not in Column A. Column A Column B Column C Column D Column E Name $$ amount Blank Name $$ amount Any suggestions? Check this out: http://img138.imageshack.us/img138/3036/nonamed.png Micky "hamdodger" wrote: > I am trying to figure out how to create a Vlookup formula that would do the > following: > > I want to find the names in Column D that are not in Column A...

CRM 1.2 Licensing questions
Hi all, We are a Microsoft Certified Partner, so my understanding is that we get a fiver user license of CRM. But which exact version of CRM is it. The suite ? Standard or Professional ? Also, do I use the keys that I get on the MSDN download page, or should I follow the instructions on the sheet that I got from MS about registering my copy. I believe these instructions are for version 1.0. Any idea when they'll have the instructions for 1.2 ready ? Thanks Nick Nick I believe the license you got from microsoft is a production license ie you can use it in your business. The MSDN ...

Replace Question
I have a long column of numerical data with occassional random occurences of text in a cell, like so: 25 37.5 48 25.9 57 NAM 65 59 24 etc. I want to replace every occurrence of "NAM" (always the same text) with the contents of the cell immediately above. In the example above I want to replace NAM with 57. Can I do this using Find/replace? Steve Sub ChangeEm() Dim cell As Range Dim FirstCell As String With Worksheets("Sheet3").Columns("A") Set cell = .Find("NAM", LookIn:=xlValues) If Not cell Is Nothing Then Do ...

Newbie Question #4
Hi. Using Excel 2003 with XP Pro. I'd like to enter numbers without having to enter the plus sign (+) first. When I enter "5+5" it treats it as text instead of giving me the total (10). Any way to change this default? TIA John Just enter =5+5 without the quotes. -- Message posted via http://www.officekb.com John, for the total the formula would have the = equal sign first the the equation to have the total show in the cell. Is that what you nee -or- what you are wanting to avoid -- juli ----------------------------------------------------------------------- julie'...

Sent Mail Question
I'm new to CRM 1.2 so patience please. It appears that if I send a mail message to a Contact that I have in an Account in CRM 1.2 that the only record of that activity lies within CRM. Looking in my Sent Mail folder I find no record of the action. Environment: SBS 2003 Exch Svr 6.5.7226 (SP1) XP-Pro desktop I send the mail within the CRMweb site as an Activity associated with a Contact. The sent mail appears in the Contact's History but for some reason MS elected not to order these by Date (at least not that I can figure out). More importantly to me, I have no single screen showi...

Filter based on unbound text box with default value
I'm using the fOSUserName module to give an unbound text box a default value of the user's network User Name. I'm trying to filter a form using this text box value (Text24), as this value corresponds to a bound field (Anumber) in the form's driving query. I cannot seem to get it to all come together. I used the below code, but, when run, it throws up a dialog box which has the unbound text box's value in it. If I enter that value in the dialog box, the form filters as I want it too. Hope that makes sense....thanks for any help. Dim strFilter As String strFilter = &...

Money 2006 Question
Hi all, My brother wants to use Money 2006 that came with his new pc, but he says that it's necessary to open a MS Passport account just in order to use Mny 2006! He is one that likes to use fake bank names & such in order to protect his files, and he says that looks to be impossibe in Mny 2006. Any thoughts about this? Regards Rusty Fender wrote: > Hi all, > > My brother wants to use Money 2006 that came with his new pc, but he says > that it's necessary to open a MS Passport account just in order to use Mny > 2006! > > He is one that likes to u...

Formula question #26
Three columns: b 1250 1253 s 1250 1253 If column a equals "b" for buy, I want to subtract column b from column c. If column a equals "s" for sell, then I want to subtract column c from column b. I'm looking for a formula in column d that will get the desired results. Thanks. If column A will contain ONLY a B or an S, you can write this IF function: =IF(A1="b",C1-B1,B1-C1) If your data begins in row 2, then in D2: if(a2="b",c2-b2,if(a2="s",b2-c2,"?")). Autofill that formula through each row in column D....

Advanced Find Gripe
When users type in a query, they would like to just hit the Enter key to initiate the search without having to click the Go button. Is there any way to give the Go button default focus on the form so that when the Enter key is hit, the search will begin. I have learned to live with this flaw by hitting the "Tab" key, then Enter, and that works. "Bobby" wrote: > When users type in a query, they would like to just hit the Enter key to > initiate the search without having to click the Go button. Is there any way > to give the Go button default focus on the ...

2 criterias filtering
hy, i need help with this: i have a table, with data, and i want to filter this table by criterias I attached an image with my table..so, i want to select in A2 fo example, first criteria...{ Nicu }... then second criteria , in B2 ...{ Marti }, .....and in C2 and D2, i want to see the all the matche for this two criteria, like in table 2.I want criteria to be dropdow boxes. Can this be done even if my table data it's random? I tried to say this as simple as posible...sorry for my gramatica errors. thank +------------------------------------------------------------------- |Filename: ta...

quick question
So I extended my Windows 2003 to support Windows 2008, making my schema version now 44. I do not wish to to install a windows 2008 DC, can I still use the features such as RODC and probably fine grain password policy etc? Howdie! On 30.03.2010 15:28, Nik wrote: > So I extended my Windows 2003 to support Windows 2008, making my schema > version now 44. > I do not wish to to install a windows 2008 DC, can I still use the > features such as RODC and probably fine grain password policy etc? No, you can't. For RODCs, you need a full-DC with Server 2008 in the hu...

Publisher Question #7
How do I overlap stacked objects so that the top object appears to be tucked INTO the object behind it? Arrange menu, Order... or alt+F6, bring to the front alt+Shift+F6, send to the back. There is also bring forward or send backward with no keyboard equivalent in the Arrange menu, Order. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "elenaperez" <elenaperez@discussions.microsoft.com> wrote in message news:27A8A0DB-B944-4CFE-B406-C12379F04522@microsoft.com... > How do I overlap stacked objects so that the top ...

junk mail filter #4
I recently installed new anti-virus software (TrendMicro OfficeScan) on our Network and since then one of my pc's junk mail filter is not working properly. The user has checked junk mail filter options and all restrictions are still there. Version of Outlook? Be more precise in what's not working as well; give an example if it's hard to explain. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "jules" <anonymous@discussions.microsof...

feedback on SPAM filtering solutions
Any one want to chime in on what are the "better" solutions for spam filtering with Exchange 2003? I have looked at GFI Mail Essentials, Symantec Mail Security, and McAfee SPAMKiller. Any suggesstions or comments are welcome. Thanx! I'm using Sybari's Advanced Spam defense. So far it's catching 99% of all spam. I'm very happy with their product. "timg" wrote: > Any one want to chime in on what are the "better" solutions for spam > filtering with Exchange 2003? I have looked at GFI Mail Essentials, > Symantec Mail Security, and Mc...