Resize A Range

You can change the size of a range using the Resize property. Typically you resize a range because it does not contain the desired number of cells. When you resize a range, you change the number of rows and columns in a range. You can change the size by specifying either more or fewer rows or columns.

The Resize property has two optional parameters of which you need to use at least one. If you do not use either parameter, Excel returns the original range. The first parameter, RowSize, indicates the number of rows in the new range. The second parameter, ColumnSize, indicates the number of columns in the new range.

When you resize the range, the upper-left corner of the original range remains the same. For example, if the original range is B1 through C4 and you resize the range to contain only 2 rows and 2 columns, B1 remains as the upper-left cell value. The range is adjusted based upon that cell creating a new range of cells from B1 to C2.

You need to know how many rows and columns currently exist in a range in order to determine how to resize it. If you are dealing with a range that you defined elsewhere, such as a named range, you can use the Count property to determine the number of rows and columns within the range, as shown in the following code: NumberofRows = Range("Named_Range").Rows.Count The Count property counts the number of rows in the named range "Named_Range" and assigns that value to the NumberofRows variable. You can use the same type of syntax with the Columns property to determine the number of columns in the range. When you know the size of the range, you can use the Resize property to modify the number of rows and columns.

RESIZE A RANGE

RESIZE A RANGE

Resize Vba

□ Create a new subroutine.

< Type Dim NumColumns As Integer, replacing NumColumns with the variable for the number of columns in the range.

Type NumRows = Range ("EmpInfo").Rows.Count, replacing "EmpInfo" with the range to count.

0 Type NumColumns = Range ("EmpInfo").Columns.Count, replacing "EmpInfo" with the range to count.

□ Create a new subroutine.

0 Type Dim NumRows As Integer, replacing NumRows with the variable for the number of rows in the range.

< Type Dim NumColumns As Integer, replacing NumColumns with the variable for the number of columns in the range.

Type NumRows = Range ("EmpInfo").Rows.Count, replacing "EmpInfo" with the range to count.

0 Type NumColumns = Range ("EmpInfo").Columns.Count, replacing "EmpInfo" with the range to count.

0 0

Post a comment