Referring to Ranges with

You can refer to a range of cells from the active cell to the end of the same column with:

Range(ActiveCell, ActiveCell.End(xlDown)).Select

Say you have a table of data, starting at cell B3, which is separated from surrounding data by an empty row and an empty column. You can refer to the table, as long as it has continuous headings across the top and continuous data in the last column, using this line of code:

Range("B3", Range("B3").End(xlToRight).End(xlDown)).Select

The effect, in this case, is the same as using the CurrentRegion property, but End has many more uses, as you will see in the following examples.

As usual, there is no need to select anything if you want to operate on a Range object in VBA. The following code copies the continuous headings across the top of Sheet1 to the top of Sheet2:

With Worksheets("Sheet1").Range("A1")

.Range(.Cells(1), .End(xlToRight)).Copy Destination— _

Worksheets("Sheet2").Range("A1")

End With

This code can be executed, no matter what sheet is active, as long as the workbook that contains Sheet1 and Sheet2 is active.

0 0

Post a comment