Excel Optimize

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.

Sub SelectDown()
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:

• xlUp
• xlDown
• xlToLeft
• xlToRight

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 (single column) range without selecting the range:

Sub MakeBold()
Range(ActiveCell, ActiveCell.End(xlDown)) _
.Font.Bold = True
End Sub