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

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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.

Select Table Column with Ctrl+Space

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.

Checkout my video on a Beginner's Guide to Excel Tables to learn more about this awesome feature of Excel.  If you have used Tables, but don't like the weird formulas, you can turn those off.

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:

  1. Hover the cursor over the top-half of the header cell until it turns into a down arrow.
  2. Then left-click to select the data body range of the column.
  3. Left-click again to include the header cell in the selection.

Select Table Column with the Mouse

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.

Select Column of Regular Range with Macro Keyboard Shortcut

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.

Other Methods?

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! 🙂

18 comments

Your email address will not be published. Required fields are marked *

  • Thank you, thank you, thank you! I knew there was a better way then just mindlessly holding ctrl-shift-down arrow, or going to the bottom, pressing Shift, going back to the top… Today I took the two minutes needed to find your blog post, and now my new best friend is ctrl-space.

    • Another great thing about this shortcut is it works on filtered columns, so after I’ve started a COUNTIF and realize that the table column I’m counting in is filtered, it doesn’t matter. I used to have to cancel the formula, go to the other table, unfilter, go back to the first table and start over.

  • I’m trying to modify your code to work on rows, but it fails. See Below:

    Sub Select_Row_With_Blanks()
    ‘Select all cells for the column of the activecell
    ‘in the current region of data (used cells)

    Dim lFirstColumn As Long
    Dim lLastColumn 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
    lLastColumn = ActiveCell.CurrentRegion.EntireRow.Find( _
    What:=”*”, _
    After:=ActiveCell.CurrentRegion.EntireRow.Cells(1, 1), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Column
    On Error GoTo 0

    ‘Find the first used row in the current region
    lFirstColumn = ActiveCell.CurrentRegion.Column

    ‘Exit if any errors in setting the rows
    If lFirstColumn = 0 Or lLastColumn = 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
    lFirstColumn = lFirstColumn + 1
    End If

    ‘Select the range within the row
    Range(Cells(lFirstColumn, ActiveCell.Rown), Cells(lLastColumn, ActiveCell.Row)).Select

    ‘Activate the orginal active cell
    If Not Intersect(rActive, Selection) Is Nothing Then
    rActive.Activate
    End If

    End Sub

    • You switched the row and column order in the Cells function. This code works for me:

      Sub Select_Row_With_Blanks()
      ‘Select all cells for the column of the activecell
      ‘in the current region of data (used cells)

      Dim lFirstColumn As Long
      Dim lLastColumn 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
      lLastColumn = ActiveCell.CurrentRegion.EntireRow.Find( _
      What:=” * ”, _
      After:=ActiveCell.CurrentRegion.EntireRow.Cells(1, 1), _
      LookAt:=xlPart, _
      LookIn:=xlFormulas, _
      SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, _
      MatchCase:=False).Column
      On Error GoTo 0

      ‘Find the first used row in the current region
      lFirstColumn = ActiveCell.CurrentRegion.Column

      ‘Exit if any errors in setting the rows
      If lFirstColumn = 0 Or lLastColumn = 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.EntireRow).Cells(1, 1).Font.Bold = True Then
      lFirstColumn = lFirstColumn + 1
      End If

      ‘Select the range within the row
      Range(Cells(ActiveCell.Row, lFirstColumn), Cells(ActiveCell.Row, lLastColumn)).Select

      ‘Activate the orginal active cell
      If Not Intersect(rActive, Selection) Is Nothing Then
      rActive.Activate
      End If

      End Sub

  • Something has changed in Excel. All of a sudden, CTRL+Shift+Down selects all the way down to the last record, even though I have a few blank cells in between. Any idea what could have changed?

  • Hi Jon

    Thanks for your great emails.

    I wonder if you would be willing to help me?

    I need to delete Rows from a spreadsheet imported from another source, the problem is that each report has different rows that need deleting each time. Sometimes it’s rows 1-6 and then 50 to 55 and then other times it’s rows 1 to 10 and perhaps 60 to 67 – sometimes more below that.

    I am not experienced with VBA – I did watch your Webinar which was great.

    But this is beyond me.

    I would be most grateful if you could help.

  • Hello Jon,
    Thanks for giving valuable excel tutorial for us. I learned basic excel through your blog and video tutorial. Again thanks for giving information regarding excel by email.

    I have one question for you.
    How can I earn in five figure (in Lacs) with MS Excel?

    Waiting for your Reply

  • Jon,

    Thanks for providing this code. Selecting a range or column with lots of blanks has always been a pain. The code you posted prompted me to think of another option.

    For me, most of the time, the data starts in row 2. As such, the code below seems to work consistently and effectively but I’m very interested in getting your feedback. Based on the active cell, it selects the range from the cell in row 2 down to the last non-blank row. Do you see any issues with this?

    Sub Select_Column()

    Dim ColNum As Long
    Dim lRow As Long

    ColNum = ActiveCell.Column
    lRow = Cells(Rows.Count, ColNum).End(xlUp).Row

    Range(Cells(2, ColNum), Cells(lRow, ColNum)).Select

    End Sub

    • Hi Jeff,

      Thanks for posting the code. This will work as long as the last cell in the used range or current region contains a value.

      The macro might produce unexpected results if there are blank cells at the end of the column of the active cell. Here is a screenshot with an example.

      Range.End Finds Last Used Cell in Column

      When I run the macro on a cell in column C, the macro is uses the Range.End or Cells.End method to find the last used cell in the column of the active cell ONLY. In this case we might want to expand that search for the last cell to the other columns in the sheet.

      We can do this with the Range.Find method. The solution I posted above uses the Range.Find method to look through all columns of the CurrentRegion, and find the last used cell in those columns. In the case of this example, Range.Find would find Row 13 as the last used Row.

      I have an article and video series that explains both Range.End and Range.Find in more detail.

      3 Ways to find the last used cell on a sheet with VBA

      Range.Find has more parameters, and looks scary at first, but it is a very powerful method to learn. There are many use cases for it.

      Here is your macro, modified with the Range.Find method.

      Sub Select_Column_Range_Find()
      
      Dim ColNum As Long
      Dim lRow As Long
      
      ColNum = ActiveCell.Column
      
      lRow = Cells.Find(What:="*", _
                          After:=Range("A1"), _
                          LookAt:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
      
      Range(Cells(2, ColNum), Cells(lRow, ColNum)).Select
      
      End Sub

      In this case it will look in all the cells in the sheet, and find the last used cell anywhere on the sheet.

      In my solution above, I modified this to only have Range.Find look in the columns of the CurrentRegion. So, if there is another table of data in other columns on the sheet, it will not account for those columns in the search for the last used cell.

      That solution doesn’t work if you have multiple tables listed in the same column down the page.

      It’s challenging to come up with a perfect solution that will work every time here…

      Please let me know if you have any questions. Thanks again and have a nice weekend! 🙂

  • Hmm, I do this with and without tables sometimes, but I’ll

    – freeze the header row,
    – use the ctrl+down on the column next to the one that has blank cells,
    – move the active cell over to that column after I’ve reached the bottom,
    – shift+click header title & shift+down.

    Now the column w/ blank cells is selected. Hope that made sense. I use this method all the time…b/c I always forget about ctrl+space. =p

  • Jon,
    OMG works like a dream, thank you! Quick question: in my VB window, in my Personal.xlsb list, I’m adding more macros but they’re all listed as “Module 1, Module 2….”), but I saw in your demo that yours actually have names reflecting the macro purpose. If I choose one of the modules, I can see in the right window the name (top right), but it’d nice to see that name instead of “Module #” in the list…is this stupid easy to do? 🙂

    Thanks for your great tutorials!

    • Hi Peter,
      in case you have not yet found it – this is in the properties of the module, there you can set the name.
      Cheers
      Ralph

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly