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.
| Method | Edge | Drawback |
|---|---|---|
| Ctrl Shift Arrow | Time-efficient when the range is contiguous | Time-consuming if there are too many empty cells within the data |
| Ctrl End | No need to guess the last row | Time-consuming if there are too many empty cells within the data |
| Go To | Time-efficient when the range is non-contiguous | Need to estimate how many rows there are |
| VBA | Almost always fastest once created | Take time to create the script |
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.
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).
1. Select column to end of data by Ctrl Shift Arrow
Step 1: 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.
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
Step 2: Press Ctrl + Left Arrow until you reached Column A
Step 3: Press Ctrl + Shift + Up Arrow until you reached your starting cell
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.
Step 2: Press Ctrl + G to open the “Go To” dialog
Step 3-4: Type the cell range into the reference box and press “OK”
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.
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).SelectTo select row 1
Range("XFD1").End(xlToLeft).SelectOther 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!
