Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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: – Molly7244 Sep 16 '09 at 21:33

Try this:


or this:

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

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

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

This averages everything but the header with the header in Cell "B1"

Dim GG as Long

GG = Application.WorksheetFunction.Average(ActiveSheet.Range("B2", Range("B2").End(xlDown)))

share|improve this answer

Your Answer


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.