vba - How to select full column except the header? -

this hardest thing me figure out, after sifting through stack overflow 4hrs no results.

this wont care if have blank rows , won't delete header.

this simple solution.

call rangea2selectandclear      call rangeb2selectandclear      call rangec2selectandclear     call ranged2selectandclear 

call above

and subs below.

sub rangea2selectandclear() range(cells(2, 1), cells(rows.count, 1)).clearcontents end sub  sub rangeb2selectandclear() range(cells(2, 2), cells(rows.count, 2)).clearcontents end sub  sub rangec2selectandclear() range(cells(2, 3), cells(rows.count, 3)).clearcontents end sub  sub ranged2selectandclear() range(cells(2, 4), cells(rows.count, 4)).clearcontents end sub 

edit "more ways of doing things so:"

incase doesnt select range.

range(cells(2, colnum), cells(rows.count, colnum).end(xlup))  

to select row of cells in (counting columns instead of counting rows)

range(cells(2, 2), cells(2, columns.count).end(xltoleft)).select 

to select range of columns excluding header use this:

range(cells(2, "a"), cells(rows.count, "d").end(xlup)).select 

or *updated 10-22-15

range("a7", cells(rows.count, "e").end(xlup)).select 

the following selects range in sheet, technically cant select range in sheet use specific action want selection. makes code faster if use way. *updated 10-22-15

with worksheets("template bom").range(worksheets("template bom").cells(7, "a"), worksheets("template bom").cells(rows.count, "e").end(xldown))     'updated 10-22-15 there no limit range now.         '.select 'if using .select code fails.         .clearcontents         .interior.colorindex = xlcolorindexnone     end 

would not better have 1 procedure works on column rather procedure each column?

sub test()      call columnselectandclear(1)     call columnselectandclear(2)     columnselectandclear 3  end sub  public sub columnselectandclear(colnum long)      activesheet         .range(.cells(2, colnum), .cells(rows.count, colnum)).clearcontents     end  end sub