Defining Ranges

Besides determining the number of rows and columns within a range you may also need to know the exact row or column where the range begins. You can accomplish this by using either the Row property for rows or the Column property for columns. You can determine the number of the first row in a range with the following code:

Example:

FirstRowNum = CurrentRange.Row

This code assigns the integer value representing the first row in the specified range to the FirstRowNum variable. You can also determine the first column in the range using the Column property as shown in this code:

Example:

FirstColNum = CurrentRange.Column

When you know what the first row and column are in the range, you can create the first cell in the range using the Cells property, as shown in this code:

Example:

Cells(FirstRowNum, FirstColNum)

This statement takes the values returned by the Row and Column properties and determines the first cell. Typically this cell is also the active cell, if you have just selected the range.

, Switch to Excel and run the macro.

■ The newly sized range is selected.

Q Type an If Then statement to determine if the range contains the appropriate number of rows and columns.

_Q Type NumRows = NumRows

+ 5, replacing + with — to subtract and 5 with the number to which you want to change the row number.

° Type NumColumns = NumColumns + 5, replacing + with — to subtract and 5 with the number to which you want to change the column number.

Type Range("EmpInfo"). Resize(RowSize:=NumRows, ColumnSize:=NumColumns) .Select.

, Switch to Excel and run the macro.

■ The newly sized range is selected.

0 0

Post a comment