Select an entire column minus header row in an Excel macro

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.

Solution 1:

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

Solution 2:

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

Solution 3:

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

Solution 4:

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.