How to extend selection to the end of column or entire row in Excel?
When working with large Excel worksheets, you may often find yourself needing to quickly select all the data in a column or across a row. For example, you might need to copy, format, or analyze entire stretches of data. Scrolling manually to the end of a long column or row, especially if you have hundreds or thousands of rows or columns, can be time-consuming and error-prone. Therefore, efficient ways to extend your selection are essential for improving productivity and accuracy. This article provides step-by-step guidance on several methods to extend your selection from the current cell to the end of a column or an entire row, ensuring you can handle both simple and more complex data tables with ease.
Extend selection to the end of column or row with shortcut key
VBA code - Automatically select to the end of a column or row, regardless of blanks
Extend selection to the end of column or row with shortcut key
Excel offers built-in shortcut keys that make it easy to extend a selection to the last filled cell in a column or row. This method is widely used because it is fast, does not require any extra setup, and works seamlessly for many typical worksheet layouts.
Office Tab
Tabbed navigation for Word, Excel, PowerPoint, and more—just like a web browser, with smooth switching in one window.
1. Select the first or any filled cell in the column you wish to extend, then press 
(Ctrl + Shift + ↓) at the same time. You will see the selection extend from the starting cell all the way to the last consecutive filled cell in the column, as shown below.
2. To extend the selection horizontally across a row, select the first or any filled cell in the row, and press 
(Ctrl + Shift + →) together. The selection will then expand to the end of the filled cells in that row, as displayed in the screenshot below.
This method is especially useful for continuous ranges of data. However, it is important to understand how Excel determines the endpoint for the selection with these shortcuts—it stops at the first blank cell encountered in the direction you are extending.
Notes:
- If the starting cell is empty, pressing the shortcut will move the selection directly to the next filled cell below or to the right, depending on the direction of the shortcut, instead of selecting the range. For example:
- If there are blank (empty) cells within the column or row, the selection will end just before the first blank cell it encounters. This means that if your data includes gaps, you may need to repeat the shortcut after the blank cell to continue extending the selection to the next block of data:
Applicable scenarios and limitations: This shortcut method works best when your data is organized in uninterrupted columns or rows. If your data includes frequent blank cells, or if you need to select data with irregular gaps, you may find that the shortcut stops short or requires multiple steps. In such cases, alternative solutions (such as using VBA macros) may help to automate the process and select all data regardless of blanks.
Practical tip: If you want to select the entire used range in a worksheet (all filled cells in both rows and columns), you can press Ctrl + A after selecting any cell within your data range. However, keep in mind this will select the entire contiguous filled region, not just down a column or across a specific row.
Troubleshooting: If the selection does not behave as expected, double-check for hidden blanks or merged cells, as these can break the contiguous selection and cause the shortcut to end prematurely.
If these built-in shortcuts do not fully meet your needs—such as, if you need to select to the end of a column or row regardless of where blank cells appear—consider using an automated VBA solution provided in the next section.
VBA code - Automatically select to the end of a column or row, regardless of blanks
For situations where your data contains blank cells, and you still want to extend your selection from the current cell to the true last cell with data (ignoring any gaps), a VBA macro offers a flexible and efficient solution. This approach can help when you have inconsistent data layouts, such as columns or rows with blank entries scattered throughout. Using this macro reduces manual navigation and ensures you do not miss any filled cell in your range.
Applicable scenarios and advantages:
- This solution is suitable if your data includes blanks and you want to select from a starting cell to the actual last filled cell in the column or row, rather than stopping at the first gap.
- It allows for automation in cases of large datasets, increasing efficiency and reducing manual errors compared to repeatedly using shortcuts.
- As a potential drawback, using VBA macros requires enabling macros and some initial setup. It is best suited for users comfortable with basic Excel VBA operations.
Steps to use this VBA macro:
1. Open your Excel workbook. Click Developer > Visual Basic to open the Microsoft Visual Basic for Applications window. If the Developer tab is not visible, you can enable it through File > Options > Customize Ribbon.
2. In the VBA interface, click Insert > Module, then copy and paste the following code into the module window:
Sub SelectToEndRegardlessOfBlanks()
Dim ws As Worksheet
Dim StartCell As Range
Dim LastCell As Range
Dim SelectRange As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set StartCell = Application.InputBox("Select the starting cell", xTitleId, Type:=8)
If StartCell Is Nothing Then Exit Sub
If MsgBox("Select by column (Yes) or by row (No)?", vbYesNo + vbQuestion, xTitleId) = vbYes Then
Set LastCell = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp)
Set SelectRange = ws.Range(StartCell, LastCell)
Else
Set LastCell = ws.Cells(StartCell.Row, ws.Columns.Count).End(xlToLeft)
Set SelectRange = ws.Range(StartCell, LastCell)
End If
SelectRange.Select
End Sub3. To run the macro, close the VBA editor and return to Excel. Press Alt + F8 to open the Macro dialog, select SelectToEndRegardlessOfBlanks and click Run. A box will prompt you to select the starting cell. After selection, choose whether you want to extend the selection down the column (Yes) or across the row (No).
The macro will automatically select the range from your starting cell to the very last cell that contains data, regardless of any blank cells in between. This helps you to quickly manage complex datasets that do not have uninterrupted stretches of data.
Notes and tips:
- If your last cell is above or to the left of your starting cell (e.g., if your starting cell is beyond the last filled cell), the macro may select only your starting cell. Double-check your selection reference if this occurs.
- You can customize the macro further to highlight or process the selected range as needed for your workflow.
- For more advanced needs, consider looping through all used columns or rows to apply this action automatically.
- If you receive an error when running macros, make sure macros are enabled in your Excel settings.
Troubleshooting:
- If the macro does not select as expected, verify you have selected an appropriate starting cell and the worksheet is not filtered or protected.
- Some special formatting or merged cells may interfere with the expected extension—review your worksheet structure if problems arise.
Summary recommendation: Choose the keyboard shortcuts for most straightforward, gap-free data, and turn to the VBA macro for dynamic data regions with blanks. Both approaches can dramatically speed up your workflow and minimize manual selection errors.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in