UsedRange in excel column

Question

How do you get a column from the UsedRange; for example column A?

xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open("C:\\base.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

//range = xlWorkSheet.UsedRange;

range = xlWorkSheet.get_Range("A") // reading from A1 to max of excel 65536?

Solution 1

This will get you Column A, which as Joe points out might be the first column in your UsedRange:

range = xlWorkSheet.UsedRange.Columns["A:A", Type.Missing]

This will get you the first Column in your range, so you can reference by number:

range = xlWorkSheet.UsedRange.Columns[1, Type.Missing]

Solution 2

@Lance's answer:

range = xlWorkSheet.UsedRange.Columns["A:A", Type.Missing] 

will give you the first column of UsedRange. If, for example, the first column that contains data is column C, it will actually return the used part of column C.

To get the used rows of the first column in the worksheet, use Intersect:

range = xlApplication.Intersect(xlWorksheet.UsedRange, xlWorksheet.Columns["A:A", Type.Missing])

This will return null if column A is not part of the UsedRange.