2 Keyboard Shortcuts to Select a Column with Blank Cells in Excel
11

2 Keyboard Shortcuts to Select a Column with Blank Cells

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

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 11 comments
Musadaq - September 13, 2017

Thanks a lot.

Useful shortcut.

Reply
Kim Meyer - September 4, 2017

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.

Reply
kamal - August 31, 2017

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

Reply
Jeff - August 31, 2017

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

Reply
    Jon Acampora - September 1, 2017

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

    Reply
Carrie - August 31, 2017

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

Reply
    Jon Acampora - August 31, 2017

    Thanks for the suggestion Carrie! I forgot to mention that there is a shortcut for selecting the Table column with the mouse. I added a section to the article above with instructions. Here is a screenshot.

    It’s a good one to know if your data is in a Table, and you don’t remember Ctrl+Space. 🙂

    Reply
      Carrie - August 31, 2017

      Well in tables I generally right click a column and go to “Select”. But that’s a neat trick too, thanks. =)

      Reply
    Mel - August 31, 2017

    Thanks for sharing. Works great for me.

    Reply
Peter Barron - August 31, 2017

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!

Reply

Leave a Reply:

Free Webinar: The 5 Secrets to Understanding Pivot Tables

x