c#,excel: getting range info

Question

hi,

i have a c# code which gets the (excel-2007) worksheets used range as follows

 Excel.Worksheet ws = (Excel.Worksheet)Globals.ThisAddIn.GetActiveWorksheet();
 Excel.Range range = (Excel.Range)ws.UsedRange;

for the current worksheet i know exactly that range is A1:HM232,...but in the future that range may change... how is it possible to get the range info such as A1:HM232 from the used range info?

i can get the rows.count and columns.count... but how to get info such as "HM"?

the reason behind this is that i'm trying to put it later on a formula like

=Sheet1!A1:HM232...

thanks in advance!

Answer 1

You could get the column index via UsedRange.Columns.Count and then do this:

var colName = String.Empty
if (ColumnIndex > 26)
{
    colName = (char)(int)((ColumnIndex - 1) / 26) + 64) & (char)((ColumnIndex - 1) Mod 26) + 65;
}
else
{
    colName = (char)ColumnIndex + 64;
}

Answer 2

I this this method should do it:

range.get_AddressLocal(range.Rows.Count ,range.Columns.Count ,XlReferenceStyle.xlA1 ,null ,ws.UsedRange);