c#: programmatically create a "named range" in worksheet scope
Assumptions:
Sheet1 has cells A1:A4, for which you want to create a named range
VBA:
Sheet1.Names.Add("tada", Sheet1.Range("A1:A4"))
I suppose it will be identical in c# (except the ;
at the end) and passing empty arguments for optional parameters.
Ishan De Silva
Updated on June 13, 2022Comments
-
Ishan De Silva almost 2 years
How can we create a 'named range' that has its scope set to a worksheet? (as we can do this manually from Excel, I guess there is a way to this in code)
Setting a name using the '
Range.Name
' property creates a workbook-scoped named range. I tried prefixing the range name with '<Sheet Name>!' as suggested here, but it doesn't work. The name doesn't get set at all that way.Any idea as to how this can be done in C#?
Update (2013/05/16): Answer by shahkalpesh worked. The original code (in VS 2010) I used is:
using Excel = Microsoft.Office.Interop.Excel; Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet; Excel.Range range = ws.Range[ws.Cells[x,y], ws.Cells[(x + height), (y + width)]]; range.Name = "MyName"; // MyName in workbook scope
The modified code that works is:
ws.Names.Add("MyName", range); // MyName in worksheet scope
Thanks, idssl.
-
Mitja Bezenšek about 11 yearsYeah it is something like shahkalpesh wrote, expcept he is missing the closing parentheses: worksheet.Names.Add("MyRange", worksheet.Range("A1:A4"));
-
AndrewWhalan almost 5 yearsAnother little "gotcha" is the scoping of named ranges. Add it to the workbook object using workbook.Names.Add(...) if you need to access it across other sheets.