Getting row indexes on Range

[Selsted]

(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.

 

[Patrick Molloy]

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]

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.

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

(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.

 

[paul.robinson]

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

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.

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

(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 -

 

[Selsted]

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;
}