Macro to Select a Range of Cells (First 1000 Cells of Column A)

These VBA macros select a block of cells quickly: the first 1,000 cells of column A, a set number of cells from the active cell, or everything down to the last entry.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Select the first 1,000 cells of column A

Sub SelectFirst1000()
    Range("A1:A1000").Select
End Sub

Select a number of cells from the active cell

Sub SelectNCellsDown()
    Dim n As Long
    n = Application.InputBox("How many cells to select?", Type:=1)
    If n < 1 Then Exit Sub
    ActiveCell.Resize(n).Select
End Sub

Change Resize(n) to Resize(n, 3) to select 3 columns wide.

Select down to the last entry

Sub SelectToLastEntry()
    Range("A1", Cells(Rows.Count, "A").End(xlUp)).Select
End Sub

This finds the last filled cell in column A, even if there are gaps in the data.

Without a macro

  1. Click the Name Box, to the left of the formula bar.
  2. Type A1:A1000 and press Enter.

Ctrl + Shift + ↓ also selects down to the next gap in the data.

Things to know

  • In your own macros, you rarely need to select cells. Working with the range directly, such as Range("A1:A1000").ClearContents, is faster.
  • Rows.Count gives the number of rows in the sheet, so the code works in every Excel version.

Related macros