Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting row indexes on Range
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting row indexes on Range
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting row indexes on Range
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*am, Selsted wrote: 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.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting row indexes on Range
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; } |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a range that indexes in a For loop | Excel Programming | |||
Vlookup using concatenated column indexes | Excel Worksheet Functions | |||
Obtain current RGB settings for color indexes 17 thru 27 | Excel Programming | |||
multiple chart indexes | Excel Programming | |||
Figuring out what the Shape Indexes are. | Excel Programming |