Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How would I access a range that corresponds to an entire column starting at row 2 (there is a header row)?

The end of the selection should be the last non-empty cell in the column.

share|improve this question
add comment

4 Answers 4

See the Microsoft Support article How to select cells/ranges by using Visual Basic procedures in Excel, #19 (How to Select an Entire Range of Contiguous Cells in a Column).

share|improve this answer
    
That selects over a million rows in the workbook i'm using even though everything beyond 300 or so are blank. –  Dave Sep 16 '09 at 20:53
    
how about: Sub LastCellBeforeBlankInColumn() / Range("A1").End(xldown).Select / End Sub –  Molly7244 Sep 16 '09 at 21:17
    
btw, plenty of cool excel stuff here: ozgrid.com/free-excel.htm –  Molly7244 Sep 16 '09 at 21:33
add comment

Try this:

Range(Range("A2"),Range("A2").End(xldown)).Select

or this:

Range(Range(Cells(2, 1), Cells(2, 1)), Range(Cells(65535, 1), Cells(65535, 1)).End(xlUp)).Select
share|improve this answer
add comment

You can use the Excel keyboard shortcuts. Try Ctrl + Home.

This would take you to the cell A1 (if you have not used the freeze pane). Come one cell down and then use the Ctrl + Shift + Down arrow buttons to select until the last entered row.

If you have to select the column value, use the right arrow button instead of the down arrow button.

share|improve this answer
add comment

Referring to this SO answer, the only safe method is the .find() method.
All other methods may give wrong results if you previously deleted some cells.

Example to get the last cells

Lastrow =  Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
Lastcol =  Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

And a specific answer to your question (assuming your data is in column 2, starting at row 2)

Range([B2], [B:B].Find("*", [B1], , , xlByRows, xlPrevious)).select

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.