Tuesday, June 23, 2009

VSTO Excel : GetRange

        /// <summary>
        /// Return a range starting from cell C
        /// The range can contain empty cell
        /// C - - - - - -
        ///  - -   - - - -
        ///  - - - - - - -
        ///  - -   - -          
        ///  </summary>
        /// <param name="ws"></param>
        /// <param name="start"></param>
        /// <returns></returns>
        public Excel.Range GetRightDownRegion(Excel.Worksheet ws, Excel.Range start)
        {
            Excel.Range curCell = start;
            Excel.Range endCell = start;
                  
            int lastCol;
            int offsetCol;
            int j = 0;

            while (curCell.Value2 != null)
            {
                curCell = start.get_Offset(0, j);
                if (curCell.Value2 != null)
                {
                    endCell = curCell;
                }
                j++;
            }
            
            lastCol = endCell.Column;

            int i = 0;
            curCell = start;
            endCell = start;

            while (curCell.Value2 != null)
            {
                curCell = start.get_Offset(i, 0);
                if (curCell.Value2 != null)
                {
                    endCell = curCell;
                }
                i++;
            }
            
            offsetCol = lastCol - endCell.Column;

            endCell = endCell.get_Offset(0, offsetCol);
                                    
            return ws.get_Range(start, endCell);            
        }
    }