Excel Select Columns/ Rows To End Of Data

Excel Select Columns/ Rows To End Of Data
Table of Contents

Sometimes we need to select the column but only the used range, instead of the whole column.

We can do it with mouse if the data size if not so big.

However, using a mouse could be really time-consuming when you have thousands of rows on your Excel sheets. It can really piss one off if one accidentally release one’s finger when selecting the cells.

In this article, I am going to show you how to select column to end of data. The same concept applies to rows. I will provide you with 4 ways to deal with different situations.

Comparison (4 ways)

I will be showing you 4 ways to select to end of column from a cell. Here I want you to take a look at the comparison table.

Each method has its edges and drawbacks. You can pick one that is most suitable for you.

If you are keen to learn, I suggest you memorising all this.

MethodEdgeDrawback
Ctrl Shift ArrowTime-efficient when the range is contiguousTime-consuming if there are too many empty cells within the data
Ctrl EndNo need to guess the last rowTime-consuming if there are too many empty cells within the data
Go ToTime-efficient when the range is non-contiguousNeed to estimate how many rows there are
VBAAlmost always fastest once createdTake time to create the script
Excel Select Columns/ Rows To End Of Data – Comparisons (4 ways)

Example

In this article, I will be illustrating how to select columns/rows to the end of the data by this example.

Most of you may have learnt that the shortcut key to select columns to the end of data is Ctrl + Shift + down arrow.

I would like to add some complexity to the situation by adding some blank cells in between different rows.

As you can see, the end of column A is Cell A15.

In Cell A1 to Cell A15, there are a couples of empty cells, which means the data is not contiguous.

Examples of Excel Select Columns/ Rows To End Of Data
Examples of Excel Select Columns/ Rows To End Of Data

Sample Workbook

Here is a Excel document you can interact with. You can play with it a bit to practise the trick.

Download the workbook to practice it by yourself!

Expected Outcome

We would like to select Column A to the end of the data (Cell A15).

Expected outcome of Excel Select Columns/ Rows To End Of Data
Expected outcome of Excel Select Columns/ Rows To End Of Data

1. Select column to end of data by Ctrl Shift Arrow

Step 1: Select your starting cell

Excel Select Columns/ Rows To End Of Data - Select your starting cell
Select your starting cell

Step 2: Press Ctrl + Shift + down arrow (don’t release any of them)

In the Gif, I am pressing down arrow one at a time to show you how it works.

When you actually use it, you can keep pressing the down arrow key and release it once you find the last row.

Excel Select Columns/ Rows To End Of Data - Press Ctrl + Shift + down arrow
Press Ctrl + Shift + down arrow

Step 3: Release all 3 keys after you select the range to the end of data

You are done.

The first method is the most straightforward one. All you need to do is just repeating the normal shortcut key.

2. Select column to end of data by Ctrl End

This one is a variation of option 1.

The concept is similar to option 1 but it is slightly different.

Step 1: Press Ctrl End

Excel Select Columns/ Rows To End Of Data - Result of option 2 step 1.png
Result of option 2 step 1.png

Step 2: Press Ctrl + Left Arrow until you reached Column A

Excel Select Columns/ Rows To End Of Data - Result of option 2 step 2
Result of option 2 step 2

Step 3: Press Ctrl + Shift + Up Arrow until you reached your starting cell

Excel Select Columns/ Rows To End Of Data -

You may consider the option 2 a bit redundant since we already have option 1.

I will tell you why it is not.

When we are using option 1, we cannot press the Ctrl + Shift + down arrow too fast since we are usually unsure of where is the end of the column.

On the contrary, we can keep pressing the Ctrl + Shift + up arrow without releasing it when using the option 1. This is because usually the start of the column is cell A1.

That is why option 2 comes in more handy in most of the time.

3. Select column to end of data by Go To

Step 1: Spot the last cell in the column

You don’t need to select the cell. You just need to know which cell is the last cell in the column.

Excel Select Columns/ Rows To End Of Data - Spot the last cell in the column
Spot the last cell in the column

Step 2: Press Ctrl + G to open the “Go To” dialog

Press Ctrl + G to open the "Go To" dialog
Press Ctrl + G to open the “Go To” dialog

Step 3-4: Type the cell range into the reference box and press “OK”

Type the cell range into the reference box.png
Type the cell range into the reference box.png

You have reached the end of the column at a few keystrokes. Now you can repeat the ctrl + shift + up arrow.

You may wonder why we would use this Go to dialog when we already have Ctrl + End.

This is because Go to dialog actually gives you greater flexibility.

With Ctrl End, the last cell used in the current Excel worksheet is selected.

With Go to dialog, you can select any of the cell at your own wish.

For instance, I would like to select cell A1 to cell A2345. The end of the column is actually cell 3000.

In this case, Go to dialog is more handy.

4. Select column to end of data by VBA

Read How to Insert & Run VBA code in Excel – VBA101 if you don’t know how to use the VBA code.

The logic here is to select the Cell A1048576.

Since Excel only has 1048576 rows, without a doubt Cell A1048576 is in the last row.

End(xlUp) works like the Ctrl + Shift + Up Arrow shortcut. This step helps you to locate the last non-blank cell in the column.

Next, the code select from Cell A1 all the way to the last non-blank cell.

Excel Select Columns/ Rows To End Of Data
Excel Select Columns/ Rows To End Of Data
Sub select_until_end_of_data()
'The script is created by Dollar Excel.
'Subscribe to Dollar Excel newsletter for more free resources like this.

Range("A1048576").End(xlUp).Select
Range(Cells(1, 1), Selection).Select

End Sub

Customize your VBA code

To select column B

Range("B1048576").End(xlUp).Select

To select row 1

Range("XFD1").End(xlToLeft).Select

Other VBA articles

How to Check/Test if Sheets Exist in Excel?

How To List All Worksheets Name In A Workbook

Do you find this article helpful? Subscribe to our newsletter to get exclusive Excel tips!

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
International Open Academy

Join Our Newsletters!

JOIN OUR NEWSLETTERs!