Search code examples
c#excelrangevstooffice-interop

c# interop excel range find first and last populated value

I have a range (one row) and I want to know column position of first and last populated value in that range. This is how I am trying to calculate the position. 

var xlRange = (Excel.Range)currentSheet.Cells[10, 1].EntireRow; // 10th row.
long firstColumn= (long)xlRange.get_End(Excel.XlDirection.xlToRight).Column;

Sheet looks like this:

enter image description here

firstColumn returns 8. Which is correct. But I dont know how to get column position of last populated cell.

Also 

long firstColumn= (long)xlRange.get_End(Excel.XlDirection.xlToRight).Column;

return 12 for firstColumn when excel is setup as below:

enter image description here

I was expecting to get 1.

Any pointer?

Solution

  • You can use the SpecialCells method available with Excel. https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-specialcells-method-excel

    In your case, you want to find the last cell so: xlCellTypeLastCell with no value i.e Type.Missing.

    Excel.Range lastCell = workSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing); 
Excel.Range myRange = workSheet.get_Range("B1", lastCell);

    lastCell should give the its row and column values.
    Hope this helps.