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.
- Select Entire Column except Header Row with Shortcut
- Select Entire Column except Header Row with Define Name
- Select Entire Column except Header Row with VBA Macro
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.
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:
#1 go to FORMULA tab, click Define name command under Defined Names group. And the New Name dialog will open.
#2 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.
#3 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.
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:
#1 select a cell in a column that you want to select entire column.
#2 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
#3 then the “Visual Basic Editor” window will appear.
#4 click “Insert” ->”Module” to create a new module.
#5 paste the below VBA code into the code window. Then clicking “Save” button.
#6 back to the current worksheet, then run the above excel macro. Click Run button.
#7 Let’s see the last result: