You're probably in the habit of using key combinations such as Ctrl+Shift+ Right Arrow and Ctrl+Shift+Down Arrow to select a range that consists of everything from the active cell to the end of a row or a column. Not surprisingly, you can write macros that perform these types of selections.
You can use the CurrentRegion property to select an entire block of cells. But what if you want to select, say, one column from a block of cells? Fortunately, VBA can accommodate this type of action. The following VBA procedure selects the range beginning at the active cell and extending down to the cell just above the first blank cell in the column. After selecting the range, you can do whatever you want with it — copy it, move it, format it, and so on.
Range(ActiveCell, ActiveCell.End(xlDown)).Select End Sub
This example uses the End method of the ActiveCell object, which returns a Range object. The End method takes one argument, which can be any of the following constants:
Keep in mind that it's unnecessary to select a range before doing something with it. The following macro applies bold formatting to a variable-sized range without selecting the range:
Range(ActiveCell, ActiveCell.End(xlDown)) _ .Font.Bold = True
Was this article helpful?