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.

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.

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:

#1 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

#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.

=$A$2:$A$1048576

select entire column except header row3

#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.

select entire column except header row4

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:

#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.

Get the position of the nth using excel vba1

#3 then the “Visual Basic Editor” window will appear.

#4 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#5 paste the below VBA code  into the code window. Then clicking “Save” button.

select entire column except header row5

#6 back to the current worksheet, then run the above excel macro. Click Run button.

select entire column except header row6

#7 Let’s see the last result:

select entire column except header row7

 

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

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

Sidebar