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?

Please, Please, Please don't make more answers when you need more input for your question. That's what comments are for on Stack Overflow. If you want to take the null entries out of the range object after you have acquired it, then please ask another question. Don't add more questions on to your specific question. — Lance Roberts, Mar 30 '11 at 22:02
Those answers need to be edits to the question. They're too long to be comments. — Bill the Lizard, Mar 31 '11 at 00:55

Lance Roberts · Answer 1 · 2011-03-30T22:04:09.503

2

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]

edited Mar 30 '11 at 22:04

answered Mar 30 '11 at 19:07

Lance Roberts

22,383
32
112
130

1

Not sure why the downvote happened, but if you want verification that this works, then see this [answer](http://stackoverflow.com/questions/5411355/how-do-i-get-an-entire-column-in-used-range/5411554#5411554). – Lance Roberts Apr 01 '11 at 18:41

score 2 · Answer 2 · answered Mar 30 '11 at 19:27

@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.

UsedRange in excel column

2 Answers2

Linked