Loop Through Excel Named Range in using C#

Question

_{Wednesday, February 25, 2009 6:13 AM}

 Hi - 

I'm developing a excel application using VSTO and C#.

So I have a named range ("myRange", rows count =1000, column count =2) on a sheet in the workbook and I just want to read through the  range row by row, and read the contents in column 1 and column 2.

Any code would be much appreciated.

Thanks,
Mark

All replies (7)

_{Wednesday, February 25, 2009 2:53 PM ✅Answered}

Vinnuu -

I'm able to get the following to work:

foreach (Excel.Range row in myRange.Rows)

{

Excel.Range cell = (Excel.Range)row.Cells[1, 1];

if (cell.Value2 != null)

System.Diagnostics.Debug.WriteLine(cell.Value2.ToString());

}

Thanks for your comments.

Mark

---

_{Wednesday, February 25, 2009 6:20 AM}

range.value2 would give you  a object[,] 2 dimensional array which can be used to loop thru for ur requirmentBe Happy

---

_{Wednesday, February 25, 2009 1:24 PM}

Vinnuu,  thanks for the comment.

Is it possible to provide sample code?

Given a range named "myRange" in sheet named "mySheet", how do I

1. refer to it?
2. move it into an array?

Thanks
Mark

---

_{Wednesday, February 25, 2009 2:06 PM}

Vinnuu -   

Okay ... I got the following code to work

Excel.Range rng = (Excel.Range)Globals.Sheet10.Application.get_Range("B45", "C80");

for (int i = 0; i < rng.Count; i++)

{

Excel.Range cell = (Excel.Range)rng[i, 1];

MessageBox.Show(cell.Value2.ToString());

}

But on the first line, how can I reference a named range : "myRange" in the worksheet which goes from b45 to c80?

Thanks,
Mark

---

_{Friday, July 15, 2011 10:59 PM}

I have the same question.  Vinnuu didn't answer your question.  Guess he doesn't know.

---

_{Friday, January 27, 2012 10:12 PM | 1 vote}

            foreach (Excel.Name cname in Globals.ThisWorkbook.Application.Names) 
            {
                if (cname.Name == "MaterialTranslator") 
                {
                    object one = cname.RefersToRange;
                    foreach (Excel.Range row in ((Excel.Range)cname.RefersToRange).Rows) 
                    {
                        // attributes or columns
                        object mValKey = ((Excel.Range)row.Cells[1, 1]).Value;
                        object mValTrans = ((Excel.Range)row.Cells[1, 2]).Value;
                        object mValLeng = ((Excel.Range)row.Cells[1, 2]).Value;
}
}
}

---

_{Friday, January 27, 2012 10:13 PM}

"MaterialTranslator" is my named Range