1

I have this bit of code to get the address of the last cell in my worksheet. It returns "$U$463", how can I get it to return just the U" part??

Rui Martins

Range newContracts = activeWorksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell);

string address = newContracts.get_Address();
phoog
  • 42,068
  • 6
  • 79
  • 117
Rui Martins
  • 2,164
  • 7
  • 33
  • 52
  • this question has some usable information http://stackoverflow.com/questions/297213/translate-an-index-into-an-excel-column-name?rq=1 – datatoo Sep 03 '12 at 17:39

2 Answers2

1

You could use a RegularExpression or the Substring method like so:

string rowId = address.Substring(address.LastIndexOf('$') + 1);
string columnId = address.Substring(1, address.LastIndexOf('$') - 1);
Laoujin
  • 9,962
  • 7
  • 42
  • 69
1

You can use string manipulation:

string columnLetter = address.Split('$', StringSplitOptions.RemoveEmptyEntries)[0];

I would rather use the column's index number instead of its letter; you can get this from the Column property. If you really need the letter identifier, you can calculate it. I'm sure I remember a built-in function for converting a column's index number (i.e., 3) to its letter identifier (i.e., "C"), but I can't find it at the moment.

phoog
  • 42,068
  • 6
  • 79
  • 117
  • That's exactly what I've been trying to get, some function that did it. The string manipulation is easy, I just wanted to do it differently. – Rui Martins Aug 31 '12 at 13:16
  • @ruimartins I think I found what I was looking for: the `ConvertFormula` function. But I can't get it to return just the letter `"D"` for the fourth column; it always returns `"$D:$D"` – phoog Aug 31 '12 at 13:40