Let’s assume you aren’t using an Excel Table, List Object, or List for your data table and you want to select the data in only one column, which means the Header row is excluded. This can be simple if there’s data in every row of the column, but what if that’s not the case?
And assume for the moment that the example above extends down several hundred, or even thousand rows. I would get tired of manually selecting the data in a column by using the Ctrl+Shift+down arrow keyboard shortcut.
So I’d probably write some VBA code to handle this pesky task and link it to a button on the worksheet. Below is the VBA code.
Dim ac As Range
Dim cr As Range
Dim col As Integer
Set ac = ActiveCell
Set cr = ac.CurrentRegion
col = ac.Column - cr.Column
If cr.Rows.Count > 1 Then
cr.Offset(1, col).Resize(cr.Rows.Count - 1, 1).Select
The active cell must be in the column you want to select when running this macro.
To insert the button in Excel 2011, 2007 (Windows), I’m using a Form control by choosing Developer > Insert, selecting the Button icon, and drawing a button on the worksheet with the help of the Alt key to snap to cell borders. In Excel 2011 (Mac) choose Developer on the Ribbon then click the Button icon and draw a button on the worksheet. The alt/control key doesn’t work to snap to gridlines.
Then I rename the button and assign the macro.
I can also assign a shortcut to the Macro instead of using a button, but the only issue with that is remembering what the shortcut is when I need to use the macro.