How to Select Entire Column except Header Row in Excel

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.

select entire column except header row1

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.

select entire column except header row2

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

select entire column except header row3

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.

select entire column except header row4

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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
select entire column except header row5

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

select entire column except header row6

Step7: Let’s see the last result:

select entire column except header row7

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.

Leave a Reply