Bottom line: Learn a few keyboard shortcuts to select all the cells in a column of the used range. This is a solution for when the Ctrl+Shift+Down Arrow or Ctrl+Shift+End shortcuts don't work.
Skill level: Intermediate
Built-in Keyboard Shortcuts Don't Always Work
Often times we want to select a single column within a range of data. If the column contains blanks, then making the selection with a single keyboard shortcut can be challenging.
Ctrl+Shift+Down Arrow doesn't work because that will select all cells to the last row in the worksheet because all cells below the active cell are blank.
Ctrl+Shift+End doesn't work because all cells to the end of the used range (cell E14) will be selected. We then have to hold Shift and left arrow a few times to only select the single column.
So, I have two techniques that can help with problem.
Method #1: Excel Tables and Ctrl+Space
The first solution is to use Excel Tables. When our data is in an Excel Table, we can use the keyboard shortcut Ctrl+Space to select the column of the active cell in the Table.
Ctrl+Space will only select the data body range of the column, meaning the header row is excluded.
With the entire column selected we can copy/paste data, apply conditional formatting, delete the contents, or take any other action on all the cells in the column.
It's also good to know that pressing Ctrl+Space a 2nd time will select the entire Table column including the Header. Pressing Ctrl+Space a 3rd time will select the entire worksheet column.
Select a Table Column with the Mouse
If you are more of a mouse user, there is also a shortcut to select the Table column with the mouse:
- Hover the cursor over the top-half of the header cell until it turns into a down arrow.
- Then left-click to select the data body range of the column.
- Left-click again to include the header cell in the selection.
It can be challenging to get the cursor in the correct spot for it to turn into a down arrow. The mouse cursor has to be in the top half of the header cell. It's pretty precise, and takes a little practice.
Method #2: Select the Column with a Macro
Even if you are an avid Table user and fan, there's a good chance that your data is not always going to be in a Table. So we need a backup plan…
We can use a macro to mimic the behavior of how Ctrl+Space works in a Table. We can then assign a keyboard shortcut to the macro.
This macro uses the CurrentRegion property of the active cell (the cell the user has selected). The current region is defined as a range bounded by any combination of blank rows and blank columns. This usually returns the used range of data that the activecell is inside of.
This means that if we have multiple ranges of data on the same sheet, we should be able to use the macro to select the entire column of the range of data that the active cell is currently in.
Here is the macro. You can copy and paste it to a code module in your Personal Macro Workbook. Then assign a keyboard shortcut to the macro. Please see the video above for details on how to assign a keyboard shortcut to a macro.
Sub Select_Column_With_Blanks() 'Select all cells for the column of the activecell 'in the current region of data (used cells) Dim lFirstRow As Long Dim lLastRow As Long Dim rActive As Range 'Store reference of active cell to activate after selection Set rActive = ActiveCell 'Exit if current region is a single cell If ActiveCell.CurrentRegion.Count = 1 Then Exit Sub 'Find the last used cell in the columns of the current region 'Attempts to account for blank rows in the data by using Range.Find 'to find the last used cell in the column of the current region. On Error Resume Next lLastRow = ActiveCell.CurrentRegion.EntireColumn.Find( _ What:="*", _ After:=ActiveCell.CurrentRegion.EntireColumn.Cells(1, 1), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 'Find the first used row in the current region lFirstRow = ActiveCell.CurrentRegion.Row 'Exit if any errors in setting the rows If lFirstRow = 0 Or lLastRow = 0 Then Exit Sub 'If the region contains a header row (bold font cells) then offset start by 1 row If Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireColumn).Cells(1, 1).Font.Bold = True Then lFirstRow = lFirstRow + 1 End If 'Select the range within the column Range(Cells(lFirstRow, ActiveCell.Column), Cells(lLastRow, ActiveCell.Column)).Select 'Activate the orginal active cell If Not Intersect(rActive, Selection) Is Nothing Then rActive.Activate End If End Sub
The macro should work most of the time. However, if there are blank cells surrounding the active cell, then the current region might be a smaller range than the data range you are trying to select. It's not always easy to figure this out with code, but CurrentRegion gets us close.
The macro uses the Range.Find method to find the last used cell in the columns of the current region. Checkout my article and video series on 3 ways to find the last used cell in a sheet with VBA for more details on this method.
If the macro doesn't work for you in a certain situation, leave a comment below with a link to a screenshot. We might be able to continue to modify and improve the macro for more data layouts.
What other methods do you use to select a column in a data range? Please leave a comment below with any suggestions or questions. Thanks! 🙂