how to know rowindex after filtering

Hi,

Via VBA i filtered a sheet. Basically i should have as result only 1 row.

How can i know what is the rowindex (figure in the grey column margin 
e.g : 873, or 34245,...) ?

thanks a lot,
Maileen
0
nospam709 (49)
3/25/2006 7:17:29 PM
excel 39879 articles. 2 followers. Follow

3 Replies
338 Views

Similar Articles

[PageSpeed] 40

One way:

Option Explicit
Sub testme()

    Dim myCell As Range
    
    Set myCell = Nothing
    On Error Resume Next
    With ActiveSheet.AutoFilter.Range
        Set myCell = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
                          .Cells.SpecialCells(xlCellTypeVisible).Cells(1)
    End With
    On Error GoTo 0
    
    If myCell Is Nothing Then
        'do nothing
    Else
        MsgBox myCell.Row
    End If
End Sub

(No validation to make sure the worksheet is filtered.)

Maileen wrote:
> 
> Hi,
> 
> Via VBA i filtered a sheet. Basically i should have as result only 1 row.
> 
> How can i know what is the rowindex (figure in the grey column margin
> e.g : 873, or 34245,...) ?
> 
> thanks a lot,
> Maileen

-- 

Dave Peterson
0
petersod (12004)
3/25/2006 7:44:42 PM
If you use one column of your data to contain the row number of each
row, you can use the Subtotal function to identify which row is
visible.

Assuming row 1 is always visible and contains your column headings,
and
assuming column A contains your row numbers.

Use the formula  =Subtotal(9,$A$2:$A$60000).  This returns the sum of
the visible
cells in column A.

Since only one row is visible at a time, the sum of the visible cells
will
be your row number.

You can also use Subtotal to identify up to two additional visible
rows.

=Subtotal(4,$A$2:$A$60000) will tell you the maximum visible value in
column A, i.e. the last visible row number.


=Subtotal(5,$A$2:$A$60000) will tell you the minimum visible value in
column A, i.e. the top visible row number.

If there are three visible rows, =Subtotal(9,$A$2:$A$60000)  -
Subtotal(4,$A$2:$A$60000) - Subtotal(5,$A$2:$A$60000) will tell you the
row number of the second of the three rows.

Maileen Wrote: 
> Hi,
> 
> Via VBA i filtered a sheet. Basically i should have as result only 1
> row.
> 
> How can i know what is the rowindex (figure in the grey column margin
> e.g : 873, or 34245,...) ?
> 
> thanks a lot,
> Maileen


-- 
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32763
View this thread: http://www.excelforum.com/showthread.php?threadid=526290

0
3/25/2006 11:11:33 PM
Maileen wrote...
>Via VBA i filtered a sheet. Basically i should have as result only 1 row.
>
>How can i know what is the rowindex (figure in the grey column margin
>e.g : 873, or 34245,...) ?

Autofilter or advanced filter? If the former, it shouldn't be too
difficult to use a MATCH formula to find the matching cell. It's more
difficult with advanced filters.

Either way, you could use a formula to return the bottommost row of
filtered data. If the table were named tbl and the first column
contains no blank cells, try

=LOOKUP(2,1/SUBTOTAL(3,OFFSET(tbl,ROW(tbl)-MIN(ROW(tbl)),0,1,1)),ROW(tbl))

0
hrlngrv (1990)
3/26/2006 1:26:37 AM
Reply:

Similar Artilces:

Advanced Filter
Hello, Does any one know how to use the Advanced Filter to extract only those cells in the column below that contain at least one character "_" 12 44_6_4 3_2 5555 In the data above I would want to see the Advanced Filter output be the two strings 44_6_4 and 3_2 I don't know if there is any way in the criteria field to accomplish this. thanks for any help Ron If criteria range is D1:D2, leave D1 empty, put this in D2 (assumes your range is A1:A100 with A1 having a heading of some sort)... =NOT(ISERROR(FIND("_",A2))) A live, online Excel Master Class i...

Does the 2003 junkmail filter work on additional mailboxes...
We have several associates that log on to view their mailbox BUT also access other mailboxes (i.e. user resumes@domain.com) using the additional mailbox feature (under the advanced tap for their email account.) Will the junk email filter work on the additional mailboxes that they have under their account? I couldn't ask for Support, via Passport, as it appears that Microsoft has not updated the serial number input box to accept the longer PID. No, when you open additional Exchange mailboxes in your profile, it only works on your mailbox. It will work on IMAP once the messages ar...

HELP! I KNOW U KNOW HOW TO DO THIS....
i was told there is a way to make certain letters equal numbers. EX. BREADNMILK is 1234567890 / B=1 R=2 and so on. This is so each product has a digit code for the price. EX. EDB = $3.51. I don't know how to do that but my boss said it's possible....THANK-YOU! I have an idea, but I'm not sure I understand what you really want, so I hate to waste the time to produce it. Could you give a few more examples? What are you doing with the data once you figure out a way to make this work? Why BREADNMILK? Is that just an example? You'll use just one word? You're trying to obscu...

It's Conditional Formatting, Jim, but not as we know it...
Hi, Is there any way of producing the same as a conditional format on a cell (ie. chenge fill and font colours according to a calculated value) but for more than three (four, if you count the default) values? TIA Dave An example '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Targe...

OWA jumping mouse
I have two computers on my network that are having an interesting issue. When they are creating a message and typing away in the body of the message, randomly when they hit a key, any key, they system will act as if the mouse was clicked. If the mouse is over the send button, it would have sent the email. If the mouse was over the start button, the start menu would have come up. Where ever the mouse was left at, it will act as if it was clicked. It is very random but often. This only happens in OWA, not in outlook or any other place. Both systems are running XP pro SP2 with IE 6 SP2. ...

html filter
Office 2003 can not use the HTML FILTER for Office 2003. Also...WORD has a Save to Filtered HTML Web Page setting which lets you save to HTML without the "extraneous" Office html code which is used for roundtripping to HTML. Unfortunately, Excel doesn't seem to have the same "Save to Filtered HTML" setting. Is there a workaround? -David ...

36200 Honeywell Air Cleaner Replacement Filter
Price:$63.99 Image: http://thediscountlocator.info/image.php?id=B0009H79B2 Best deal: http://thediscountlocator.info/index.php?id=B0009H79B2 For optimum performance replace filter every 12 months. SIMILAR PRODUCTS: 28600 Honeywell Air Cleaner Replacement Filter:http://thediscountlocator.info/index.php?id=B0006OJ26I ...

Do You Know???
Hi... Where to get personal / business loan? How to settle your debt? Where to invest? Monthly Profit 20%? One time investment only? Setup your own business & growing income? The Beneficiary Bank is Barclays Bank? Just only need USD1070 for Financial Freedom? 300% Profit,10%Commission,5% Bonus,1% Revenue & 0.01% Daily Profit Payout!! ---------------------------------------------------------------------------= =AD=AD----- Get me online at : E-Mail /Yahoo Messenger,my ID : sallehes[at]yahoo.com Change [at] to @ If you never take the chance, you will never have the chances. >From O...

Filtering a list
Hello. I have a list of 'N' number of names, phone numbers, addresses, which looks like the following: Serial Name Phone Address ------ ---- ----- ------- 1 A 44656 Somewhere 2 B 41464 Someplace 3 C 65465 Nowhere .. . . . .. . . . .. . . . N NN 45646 Noplace Now I need to single out some of the phone numbers and corre that matches the serial numbers which I have put in another column. So, how do I copy the entire rows that matches the phone numbers I have placed in a separate column? I don't know whether I am clear enough or not, but any help will be much appreciated. Need...

Employee Filters
Hello: On the employee filters in the HR Preferenes window, would this restrict seeing employee information in both Payroll and HR? Also, would it restrict reporting on certain employees in a division or department? Thanks! childofthe1980s Your message does not indicate which version of Great Plains you are using. Using 8.0 with SP3 applied, HR filters do apply to some of employee information outside of HR. The settings will filter selected forms, Smartlists and Lists items. The HR filter setting do not apply to reports outside of HR. The filtered forms are: Employee Maintenance,...

Does any know how to add more columns in the NCAA Excel Tracker?
trying to add more columns in the NCAA Tournament Tracker but it won't keep the formulas correct. Any help Just what exactly is the/a "NCAA Tournament Tracker"? -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "NCAA Tourney Commissioner" <NCAA Tourney Commissioner@discussions.microsoft.com> wrote in message news:8020B969-D6D4-4857-96E4-3475D1F75DA6@microsoft.com... trying to add more ...

Help with Advanced Filter criteria for customized email inbox view
Using MSOutlook2003 I customized my Outlook Inbox view with an advanced filter. The criteria were Field Condition Value -------- ----------- ------ Due By does not exist Due By on or before Today() The result was that I see all of my email messages that either don't have a reminder set (flag and due date) or have a reminder set for any time after 12:00:00 AM of the current day. This worked very well. Everything that is due on a future day was hidden from view so I didn't have to look at it. I then decided that I also wan...

Do you know this?
I gonna to purchase Joyfax Server. Do you have any idea about it? 'Joyfax - Fax Server, Fax software, Windows Fax Server, Network Fax Solution, Free Fax Viewer, Fax Broadcasting software' (http://www.joyfax.com) -- lenny66 Posted via http://www.vistaheads.com In article <lenny66.4cslw5@no-mx.forums.vistaheads.com>, lenny66.4cslw5 @no-mx.forums.vistaheads.com says... > What exactly would you imagine yourself seeing subject "Do you know this?" People usually ignore topics with unclear subjects. -- Poutnik The best depends on how the be...

do you know??
hi I have a problem I had made a programm deal with database I had created it manually by microsoft access. I want the programm make the database automatically when it run on every computer?? second did every computer run this programm have microsoft access ??? goodbye thank you ??? 1. You should just ship the mdb file with your program. 2. you don't need to have access installed on all the computers just the odbc/ado drivers for access. AliR. "mohaz" <mohaz14@yahoo.com> wrote in message news:1187291340.459954.57930@k79g2000hse.googlegroups.com... > hi >...

Filter customerid
Hello, When I choose to sort the sales transaction records by "customerid", in the Sales Transaction Entry screen, I would like to further filter the records based on the starting character of the customerid. I would want to accomplish this using VBA. In other words if the customerid is chosen to be the sortby option, then if I click on the nextbuttontoolbar/previousbuttontoolbar/ endoffilebuttontoolbar etc, I would want to filter the records based on the starting character of the customerid, in addition to the sort by customerid. Thanks ...

Filter XmlDocument based on XPath expressions
Hello, I have a very complex XmlDocument. I want to create a new XmlDocument that contain only certain certain elements, based on a set of XPATH expressions I have. What is the easiest and least performance impacting solution for this using the .NET implementation of DOM? Thanks! Dinesh wrote: > I have a very complex XmlDocument. I want to create a new XmlDocument that > contain only certain certain elements, based on a set of XPATH expressions I > have. > > What is the easiest and least performance impacting solution for this using > the .NET implementation of ...

I would like to know how to uninstall Outlook Express 6!
When I purchased my computer, it was installed with Outlook Express 6, and I don't use the program. I would like to know how to uninstall it. Please reply if you know how! If you are running Windows XP with Service Pack 1 or Windows 2000 with Service Pack 3 or 4, try going to the add/remove applet in the control panel. Choose Set Program Access and Defaults. Under the email section, uncheck the box to show the icon. (It will always remove the OE icon.) Note: If Microsoft Outlook is your default mail program, it will try to push newsgroup support to OE (started as a news only clien...

EARN $ 1,434,892.8 BY E-GOLD (GUARANTEED!!) All you need is 3 People You know #13
EARN $ 1,434,892.8 BY E-GOLD (GUARANTEED!!) All you need is 3 People You know Hello every body , im be glad to introduce one of the best MLM for every body who wants to explod his/her earning with very simple steps and incredible situation! JUST IMAGINE...... If you achieve only 10% success you will still make $ 143,489.28 If you achieve only 1% success you will still make $ 14,348.928 But If we trust to each other and be serious, reaching to $ 1,434,892.8 is not seem so far,just three or four weeks!! The whole works that you should do : 1) You should open an e-gold account ,th...

Connection Filtering #4
I'm using IMF with the IMFAM tool on SBS2003 SP1 /Exchg SP2. I want to create white lists at the exchange level (3rd party mail client used which uses pop3 to collect mail from exchange). I would like to clarify that this should be done at the connection filter level by IP address only. ...

How To Know
Hi All Is there any option to know,who has open file? and Is that possible to Close also. thanks Amrit On 20/12/2009 23:51, Amrit wrote: > Hi All > Is there any option to know,who has open file? > > and Is that possible to Close also. Use Process Explorer. -- Dee Earley (dee.earley@icode.co.uk) i-Catcher Development Team iCode Systems Hi Dee I don't understand How To? "Dee Earley" <dee.earley@icode.co.uk> wrote in message news:uurKktigKHA.1824@TK2MSFTNGP04.phx.gbl... > On 20/12/2009 23:51, Amrit wrote: >> Hi...

Filtering a filter list
I'm using Excel 2000. I have used advanced filtering to extract a se of data from one worksheet to another worksheet. I now want t eliminate some of this filtered data to further refine my search. If use the auto filter feature ("does not contain") on this new workshee it does what I need but only allows for 2 criteria and I have 5 or mor that I need to filter out. Is there a way to expand the auto filter to allow more than 2 filters? If not, I assume I can create a criteria table, but what is the forma for "does not contain"? Thanks -- Message posted from http...

anyone know where to set the transaction number format?
Hi, Deos anyone know where to set the transaction number format. I need to set the transaction number from 7 digit to 6 digit. regards Dennis Dennis, The Transaction number started with 1. Are you saying that you have used a million numbers already? SO Administrator | File | Connect | put in your password | Database | Set next Transaction Number -- * "Dennis" <Dennis@discussions.microsoft.com> wrote in message news:DB499B32-9A24-4090-896E-DF0644467DCC@microsoft.com... Hi, Deos anyone know where to set the transaction number format. I need to set the transaction numb...

Anyone know how to setup a reverse mortgage in Lifetime Planner?
All I've managed so far is to just setup an income account based on projected figures for my home, but I don't know how to have this reflected in net value of the home. Thank you! This is a great question for the newsgroup--I don't recall it having come up before. I suspect it will come up more in the future. My basic thought is to setup a Liability Account and make the payments from the lender transfers from the liability to a cash account. The liability grows. Maybe you even add the accumulating unpaid interest to the liability account? (Since it defers for tax purposes,...

OT: Anybody in here do much with COM? Need to know what newsgroups...
....to use to post the following question: "What is the relationship between deploying and registering a typelib for your COM components and a proxy for said components?" Thanks, WTH "WTH" <nospam@spamtheman.com> wrote in message news:e88StatPFHA.3076@tk2msftngp13.phx.gbl... > ...to use to post the following question: > > "What is the relationship between deploying and registering a typelib for > your COM components and a proxy for said components?" > > Thanks, > > WTH > > Try the microsoft.public.vc.atl newsg...

How do I know?
How do Iknow whetehr my OE installation is uptodate? If it is older...how do i get the update...to the current version seena Your message headers show it is up to date: X-Newsreader: Microsoft Outlook Express 6.00.2900.5843 x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.5579 There will never be any changes or improvements to Outlook Express as all production ceased June, 2006. There is a chance of a security update in the future if needed, but all you need to do is make sure you keep fully patched at Windows Updates. -- Bruce Hagen MS-MVP [Mai...