Getting row indexes on Range

(I refer to C# code, but answers in VB are welcome)

I have a Range in Excel, which includes several cells (the cells the user 
selected in the Excel sheet). The range might include the following cells A2, 
B7, G4. This means that the cells might not be connected.

If I look at myRange.Cells.Count, it will return 3. If I look at 
myRange.Row, it will return 2 (if A2 was the first selected row by the user).

Now, I need to get the row numbers of all selected rows, so in the above 
range, I need an int[] of {2, 7, 4}. But I can't see any solution to go 
through the Cells and get the row index for the individual cell.
0
Utf
2/10/2010 9:20:01 AM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
1151 Views

Similar Articles

[PageSpeed] 40

the Rows.Count property should return 3 as well

for what you need to do, I suggest a loop

dim cell as Range
dim text as string
FOR EACH cell in myRange
text = text & "," & cell.Row
NEXT

text will hold the address row

"Selsted" wrote:

> (I refer to C# code, but answers in VB are welcome)
> 
> I have a Range in Excel, which includes several cells (the cells the user 
> selected in the Excel sheet). The range might include the following cells A2, 
> B7, G4. This means that the cells might not be connected.
> 
> If I look at myRange.Cells.Count, it will return 3. If I look at 
> myRange.Row, it will return 2 (if A2 was the first selected row by the user).
> 
> Now, I need to get the row numbers of all selected rows, so in the above 
> range, I need an int[] of {2, 7, 4}. But I can't see any solution to go 
> through the Cells and get the row index for the individual cell.
0
Utf
2/10/2010 10:13:01 AM
I tried two things according to this (simplified here).

            foreach (Range cell in myRange)
            {
                int rowNumber = cell.Row;
            }

            while (true)
            {
                int rowNumber = myRange.Row;
                myRange = myRange.Next;
            }

Both solutions loops indef (I know there is a while true), and rowNumber 
continues to be assigned the same value.

I didn't think of the foreach as a possible solution, and unfortunately, it 
seems as it doesn't work.




"Patrick Molloy" wrote:

> the Rows.Count property should return 3 as well
> 
> for what you need to do, I suggest a loop
> 
> dim cell as Range
> dim text as string
> FOR EACH cell in myRange
> text = text & "," & cell.Row
> NEXT
> 
> text will hold the address row
> 
> "Selsted" wrote:
> 
> > (I refer to C# code, but answers in VB are welcome)
> > 
> > I have a Range in Excel, which includes several cells (the cells the user 
> > selected in the Excel sheet). The range might include the following cells A2, 
> > B7, G4. This means that the cells might not be connected.
> > 
> > If I look at myRange.Cells.Count, it will return 3. If I look at 
> > myRange.Row, it will return 2 (if A2 was the first selected row by the user).
> > 
> > Now, I need to get the row numbers of all selected rows, so in the above 
> > range, I need an int[] of {2, 7, 4}. But I can't see any solution to go 
> > through the Cells and get the row index for the individual cell.
0
Utf
2/10/2010 10:59:01 AM
Hi
you need to look at Areas

Sub tester()
Dim myArea As Range
For Each myArea In Selection.Areas
    MsgBox myArea.Row
Next
End Sub

regards
Paul

On Feb 10, 10:59=A0am, Selsted <Sels...@discussions.microsoft.com>
wrote:
> I tried two things according to this (simplified here).
>
> =A0 =A0 =A0 =A0 =A0 =A0 foreach (Range cell in myRange)
> =A0 =A0 =A0 =A0 =A0 =A0 {
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 int rowNumber =3D cell.Row;
> =A0 =A0 =A0 =A0 =A0 =A0 }
>
> =A0 =A0 =A0 =A0 =A0 =A0 while (true)
> =A0 =A0 =A0 =A0 =A0 =A0 {
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 int rowNumber =3D myRange.Row;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 myRange =3D myRange.Next;
> =A0 =A0 =A0 =A0 =A0 =A0 }
>
> Both solutions loops indef (I know there is a while true), and rowNumber
> continues to be assigned the same value.
>
> I didn't think of the foreach as a possible solution, and unfortunately, =
it
> seems as it doesn't work.
>
>
>
> "Patrick Molloy" wrote:
> > the Rows.Count property should return 3 as well
>
> > for what you need to do, I suggest a loop
>
> > dim cell as Range
> > dim text as string
> > FOR EACH cell in myRange
> > text =3D text & "," & cell.Row
> > NEXT
>
> > text will hold the address row
>
> > "Selsted" wrote:
>
> > > (I refer to C# code, but answers in VB are welcome)
>
> > > I have a Range in Excel, which includes several cells (the cells the =
user
> > > selected in the Excel sheet). The range might include the following c=
ells A2,
> > > B7, G4. This means that the cells might not be connected.
>
> > > If I look at myRange.Cells.Count, it will return 3. If I look at
> > > myRange.Row, it will return 2 (if A2 was the first selected row by th=
e user).
>
> > > Now, I need to get the row numbers of all selected rows, so in the ab=
ove
> > > range, I need an int[] of {2, 7, 4}. But I can't see any solution to =
go
> > > through the Cells and get the row index for the individual cell.- Hid=
e quoted text -
>
> - Show quoted text -

0
paul
2/10/2010 12:15:17 PM
After trying a lot of different things, and close to giving up, the following 
solved it:
(The range.EntireRow.Select() is not mandatory, and you should check that 
Selection does in fact return a Range.)


        private List<int> getSelectedRows(Worksheet sheet)
        {
            List<int> listRet = new List<int>();

            Range range = (Range)sheet.Application.Selection;

            range.EntireRow.Select();
            range = (Range)sheet.Application.Selection;

            for (int i=1; i<=range.Areas.Count; i++)
            {
                Range internalRange = range.Areas.get_Item(i);

                int firstId = internalRange.Row;
                int lastId = firstId + internalRange.Rows.Count;

                for (int j = firstId; j < lastId; j++)
                {
                    if (!listRet.Contains(j))
                    {
                        listRet.Add(j);
                    }
                }
            }

            return listRet;
        }

0
Utf
2/10/2010 12:48:01 PM
Reply:

Similar Artilces:

Command Within SQL to get Windows login Name
is there a way we can know whats the windows login accountname using a command within sql (just like select suser_name() ) , if he is still accessing the SQL server with a SQL account like 'sa' any sys table maps this info and stores ? Unfortunately not. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Madhava" <Madhava@discussions.microsoft.com> wrote in message news:9FDC0B11-E9B0-4BC6-891B-9DDA9847A203@microsoft.com... > is there a way we can know whats the windows login ...

Fill scroll window with range from procedure?
Hi All, I have been trying to fill a scroll window from a form procedure. The problem I am running into is that when I call the 'fill' statement, the executing stored procedure passes two null variables instead of the values I set for the range. I know the code works as I can put it on the Form's Pre event and the scroll window is populated with the correct restrictions, but I moved the code to a form level procedure so that many forms could call the procedure and have the scroll window filled. Here's the code: range clear table POP_WisdmActuals; 'PO Number' of ta...

Problem with Excel printing
I am using a HP LaserJet 1200 series printer with Winoow XP Pro no SP2. It has the default printer driver. I am using Parallel connection. I am using A4 paper. My problem is as follows, Sent to printer - Resolution not supported What can be wrong? "clueless" wrote: > I am using a HP LaserJet 1200 series printer with Winoow XP Pro no SP2. It > has the default printer driver. I am using Parallel connection. I am using A4 > paper. > > My problem is as follows, > > Sent to printer - Resolution not supported > > What can be wrong? > ...

Deleting Range name's listed in the range address box.
In Excel, in the top corner of the application there is a box which list the name of a select range (e.g F19 etc..). when I use the querytables method to import a CSV onto a worksheet. This range address box, has the name of the data I have imported. when I use clear, or qyertytables delete to remove the imported data - I am unable to remove the name of the data appearing in the drop-down list for the range box. Is there anyway to delete this ? or is it not a problem ? I am concerned because I this rangelist seems to fill up pretty quickly with imported data ranges.. Choose Insert&...

Row and Column heading
I want to copy the row and column heading with the data and paste them Rolsha More detail would be helpful. Which row and column heading? The Excel headers like A,B,C and 1,2,3? If so, why? Or ones you have entered yourself like Name, Address, Phone No. etc? To where would you like to paste? Gord Dibben Excel MVP On Wed, 10 Dec 2003 15:26:05 -0800, Rolsha <roland.shami@buseco.monash.edu.au> wrote: >I want to copy the row and column heading with the data and paste them ...

Getting data out of and into a dialog while it's "active"
Situation: I put up a dialog that contains, among other things, 2 drop-list combo boxes and on dropdown combo box. there's also a button nearby which the user can press to bring up another dialog to allow selecting the values for these three items using a different set of controls. I did something like this earlier when I had a multi-line edit control and a button to load the data from a file. When the user pushed the button, I used a file-open dialog to get the file and its contents and stuffed it into the CString variable and then returned to the main dialog. This worked perfectly. ...

How do I get averages based on specific fields.
Given data such as below, Is there and easy way to find and display in a single cell: what is the average time (Hours:mins) for all tickets of Complete status. and what is the average Hours:mins for all tickets of status=picked AND of the type FWSnap, ? Thanks, Crzzy1 ticket Date Time Status TYPE Hours:mins 44738 11/2/2008 4:45 Opened FWRules 0:00 44738 11/2/2008 6:41 Picked FWRules 1:56 44738 11/2/2008 7:14 Approved FWRules 2:29 44738 11/3/2008 0:46 Com...

Insert multiple rows
Hi, is there a way to insert multiple rows (that conatin certain column values) for each existing row in a worksheet? Thanks. -- Michael Hi Michael, See insert rows maintainining formulas http://www.mvps.org/dmcritchie/excel/insrtrow.htm it is set up to retain formulas and clear constants, but you can modify which columns get cleared so you can maintain some of the constants. ---- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm ...

How to get the value of AD user attributes in vbscript?
i want to get the field value of the AD user address attributes in VBscript. just like city, country, etc. how can i get it? thank you very much. Kino, refer to http://gallery.technet.microsoft.com/ScriptCenter/en-us/4d192f4d-2830-4a3e-9352-64a7e696a36e The ones you are interested in are "streetAddress,postOfficeBox,l,st,postalCode,c" hth Marcin "Kino" <Kino@discussions.microsoft.com> wrote in message news:C08BB049-8F6F-462F-B6C8-FB753CEFCA7C@microsoft.com... >i want to get the field value of the AD user address attributes in >VBscript. &...

Is it Possible to get rid of a System created Realtionship
The situation is that I would like it so that opportunities will only be able to be associated with Accounts. Currently they can be associated with Accounts or Contacts. Anyone have any ideas on how I could accomplish this since I cannot delete the system created relationship between Contacts and Oppurtunities. What you can do is hide this relationship from the user (remove the original lookups and set the relationship on the other end to no be displayed in the menu). Then create a new relationship between account and opportunity. If need be you can copy the value from the new looku...

Can I still get Visio 2003 trial download?
My computer has all Office 2003 products on it, but no Visio. I'm not wild about downloading a trial of Visio 2007 due to possible conflicts & issues with my 2003 products, plus the added learning curve. Is Visio 2003 still available anywhere for download? I haven't been able to find it. Thanks, -- Scott I'd suspect you'll have no luck in getting the 2003 trial version. Michael "ScottH" <ScottH@discussions.microsoft.com> wrote in message news:E0FF495E-19C6-4096-9AB7-EADD6FA59A1F@microsoft.com... > My computer has all Office 2003 products on it, ...

INDEX, FIND.... MATCH????
I posted this question once but the answer I got returned #NA. So I'm asking for help again.... I need to search one column of worksheet 3 and find where the text "car" occurs in c4:c44, for whichever cell in column c contains the text "car" (if it is in c34), I need it to return the value contained in column b (the value I would be looking for in this example would be b34. This information will be pulled from sheet 3 of a worksheet and posted in sheet 1. =index(Sheet3!$b$4:$b$44,match("car",Sheet3!$c$4:$c$44,false)) This will return #NA if 'car&#...

Export range to text file
I need to export a range from excel to a text file with a delimiter such as a comma or semicolon. I want to be able to have some kind of macro that will check each row and if a row is blank then it will stop there and only export the rows that has data. Which is why I was thinking of using a range, or array. I do have some formulas in the cells so do not want that to show up in the export. Thanks, -- Matt Scheperle mscheperle@gmail.com I have this code that I use to create a csv for later update some values in a system I hope it works Dim ColId As Integer Di...

multiple ranges & criteria for SUM-IF challenge
Not sure if this is the function for the job but I need to have SUMIF compare values in multiple ranges/columns and add if the values match in those columns e.g. if the value in range A5:A10 = value in P5 and value in range B5:B10 = value in Q5 and value in range C5:C10 = value in R4 and value in range D5:D10 = value in S5 and value in range E5:E10 = value in T5 sum range being Z5:Z10 I hope this makes sense. Can clarify further on request. Thanks Hi Try =SUMPRODUCT((A5:A10=P5)*(B5:B10=Q5)*(C5:C10=R4)* (D5:D10=S5)*(E5:E10=T5)*Z5:Z10) -- Regards Roger Govier "akumudzi" <...

still trying to get this to work
I REALLY need to get back to sorting books, but I've not been able to use windows mail in all the time I've had the computer and it is getting old! Glad/sad to see that I'm not the only one! "Barbara Harrison" <barbarakay@hughes.net> wrote in message news:uvqo5KTHLHA.5684@TK2MSFTNGP02.phx.gbl... >I REALLY need to get back to sorting books, but I've not been able to use >windows mail in all the time I've had the computer and it is getting old! >Glad/sad to see that I'm not the only one! Use the settings outlined here for se...

(Publisher 2003) How do I get the two pages to print on to one pag
I need 2 pages to print on one page. I did see on a document that someone else made on my computer that the page indicators (at the bottom left; to switch between the two pages) that the folded part of the 2nd page was turned a different way, and I know that's exactly what I need to do, but I'm not sure how to. Help please? Thanks! Vampressjaden In Publisher 2000 (may be the same for other versions) File Page Setup Special fold Horizontal It will ask you if you want 4 pages - yes -- Don Vancouver, USA "Vampressjaden" <Vampressjaden@discussions.micros...

How to get Outlook to open automatically when computer loads up?
How can I get Outlook to open up automatically when I turn on my computer? On Sun, 6 Sep 2009 12:41:01 -0700, hgonzale <hgonzale@discussions.microsoft.com> wrote: > How can I get Outlook to open up automatically when I turn on my computer? This is not really an Outlook question. You get any program or programs to start automatically when you boot the computer and you can do each of them the same way. The easiest way to do this is simply to put a shortcut to each program you want to start automatically in the Startup folder: C:\Documents and Settings\your user name\Start Menu\Pro...

I can't get the emailaddress of an account
I'm creating my own pages. But with the <attributes-all> The e-mail of the account does not exist. How can I get the e-mail of an account? First, make sure you're using <all-attributes> or the specific email address you want. Then, if there's a non-NULL email address in one of emailaddress[1-3], you'll get it back. If they are all NULL the platform won't return the empty elements. "Alper Can" <alperc@datasistem.com.tr> wrote in message news:e3JzmsuGEHA.712@tk2msftngp13.phx.gbl... > I'm creating my own pages. But with the <attribut...

Outlook 2000 getting day of week with today date
A while ago my Outlook displayed the day of week. I cant find a reference to this in the manuals, help, MS pages, and I'm not even going to try Google TIA Steve L. Check your Long Date format in Control Panel | Regional and Language Options (the actual Control Panel command for this may differ depending on your version of Windows). -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:3F90AB03.122C2BCC@mts.net, Steve Lenaghan wrote: > A while ago m...

Getting custom commandbar to show on Add-ins tab
I've created a custom toolbar for Access 2007 with the following code: Sub CreateToolbar() On Error Resume Next Application.CommandBars("PracticeCommandBar").Delete On Error GoTo 0 Dim cbar As Office.CommandBar 'I have the needed reference to Office library Dim cntrl As Office.CommandBarControl Set cbar = Application.CommandBars.Add("PracticeCommandBar", msoBarTop, _ Temporary:=True) Set cntrl = Application.CommandBars("PracticeCommandBar").Controls.Add( _ msoControlButton, Temporary...

Macro error, range object need data?
Hi all How can I improve my macro which has a macro error like below: "Macro error The method can't be used in the applied context. Specifically, some Range object methods require that the range contain data. If the range does not contain data, the method fails." How can I modify range object which has some empty data in the range? Thanks Daniel Hi, Does the range you are referencing contain data? Empty cells or those containing #N/A can cause problems when using the charting section of the object model. Cheers Andy Daniel wrote: > Hi all > How can I improve ...

Search Row Multiple Values
In row 2 from A to Z I have numbers from 1 to 12. Some numbers appear more than once, sometime 4 times. I want to have an equation that will be able to look through the ro and pic out all of the cells which have the number 4 and return thei column number. Any ideas -- Message posted from http://www.ExcelForum.com One way is to use data>filter>autofilter -- Don Guillett SalesAid Software donaldb@281.com "ianripping >" <<ianripping.157tpr@excelforum-nospam.com> wrote in message news:ianripping.157tpr@excelforum-nospam.com... > In row 2 from A to Z I have nu...

Can't get Outlook Express to work and passwords have been changed??
I am pulling my hair out to work out why Outlook express won't send/receive my emails. I have entered the POP/SMTP info as directed by tiscali but this does not work. Then to top it off my password has now changed and I have no idea what it is?? Is there anyway I can find out what it now is?? And does anyone know how to make Outlook express work with Tiscali?? This PC has cost a small fortune and I am unable to call a premium rate number to get help?? Can anyone offer any advise?? Thanks Paul ...

Can't insert Rows?
When I try and insert a row I get a prompt saying "To prevent possible loss of data, Excel cannot shift non-blank cells off the worksheet. Try to locate the last non-blank cell by pressing CTRL+END and delete or clear all cells in between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used." Can anyone advise (a) Why this happens and (b) How to stop it happening as I've tried doing what the prompt says and can't seem to solve it...and I really need to insert a row, rather than cut & paste. Any help great...

Column mailing list needs to be converted to rows
I have been handed a list that I need to convert and I am stumped. The worksheet contains three columns of addresses. The addresses are generally in one of three formats: Company Contact Street number and name City State Zip or Name Street number and name City State Zip or The repeat of the above with two lines for street information. As far as I can tell so far, these four are the only the different formats. Some of the addresses have a comma between the city and state. One final complication, some addresses use the 5 digit zip, some use the zip+4 with a dash. Is there a way to con...