This post will guide you how to select entire column except header or first row in Excel 2013/2016. How do I select whole column except first header cell with VBA Macro in Excel. How to use shortcut keys to select entire column in Excel.

1. Select Entire Column except Header Row with Shortcut

If you want to select Entire Column except Header row and also excluding all blank cells in your worksheet, you can use a shortcut keys to achieve the result. Just select the first cell except header cell, and press Shift + Ctrl + Down keys.

You should see that all cells except header cell and all blank cells have been selected in your worksheet.

2. Select Entire Column except Header Row with Define Name

If you want to select entire column except header row only, you can create a define name with the following refers:=$A$2:$A$1048576 . Just do the following steps:

Step1: go to FORMULA tab, click Define name command under Defined Names group. And the New Name dialog will open.

Step2: enter one name called select_entire_column_except_header in the New Name dialog box, and then type the following formula into the Refers to text box, and click Ok button to apply these changes.

=$A$2:$A$1048576

Step3: select the name that you defined just now from the Name Box. You would see that the entire column except the first cell or header row has been selected.

3. Select Entire Column except Header Row with VBA Macro

You can also use an Excel VBA Macro to achieve the same result of selecting entire column but the first cell . Just do the following steps:

Step1: select a cell in a column that you want to select entire column.

Step2: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step3: then the “Visual Basic Editor” window will appear.

Step4: click “Insert” ->”Module” to create a new module.

Step5: paste the below VBA code  into the code window. Then clicking “Save” button.

Sub SelectEntireColumn()
    eIndex = Application.ActiveCell.Column
    eRowIndex = Application.ActiveSheet.Cells(Rows.Count, eIndex).End(xlUp).Row
    Range(Cells(2, eIndex), Cells(eRowIndex, eIndex)).Select
End Sub

Step6: back to the current worksheet, then run the above excel macro. Click Run button.

Step7: Let’s see the last result:

4. Video:Select Entire Column except Header Row

In this video, we will showyou how to use a shortcut, a Defined Name and a VBA Macro to help you easily select the entire column except for the header row in Excel 2013/2016/2019.