VBA Resize

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

Excel VBA Resize

Resize is a property available in VBA to change or resize the range of cells from the active cell as needed. For example, assume you are in cell B5. If you want to select 3 rows and two columns from this cell, we can change the range size by using the RESIZE property of VBA.

Excel VBA Resize

Syntax of VBA Resize Property

Below is the syntax of the VBA RESIZE property.

Range().Resize(, )

First, we need to supply from which cell we need to resize using the Range object.

Then use the Excel VBA Resize property. In this property, we need to supply row and column size limits. Based on the provided row numbers and column numbers, it will resize them.

Examples of using Resize in VBA

Below are examples of using resize in Excel VBA.

Example #1

Assume you have data from A1 to B14 cells. For example, from the A1 cell, if you want to select 3 rows down and two columns left range, we can do this by using the RESIZE property in Excel VBA.

Below is the data we are using for this example.

First, we must supply the first cell reference or starting point using the RANGE object. In this example, the starting point is the A1 cell.

Code:

Sub Resize_Example()

 Range ("A1").

End Sub

For this range, use RESIZE property.

Code:

Sub Resize_Example()

 Range ("A1").Resize(

End Sub

The first argument of RESIZE is Row Size, so we need to select 3 rows of the data and supply the numerical value of 3.

Code:

Sub Resize_Example()

Range ("A1").Resize(3,

End Sub

The next argument is Column Size. For this, enter how columns you need to select. For example, we will enter 3 columns.

Code:

Sub Resize_Example()

 Range ("A1").Resize(3,3)

End Sub

Once the resizing is complete, we need to supply what we need to do with this range. We will choose the "Select" method to start.

Code:

Sub Resize_Example()

 Range("A1").Resize(3, 3).Select

End Sub

Run the code and see how many rows and columns it would select.

As you can see above from the A1 cell, it has selected three rows down and three columns to the right.

Example #2

Now, take a look at the below VBA code.

In the above code for Row Size, we have supplied blank cells. For Column Size, we have supplied 3.

Code:

Sub Resize_Example()

 Range("A1").Resize(0, 3).Select

End Sub

Run the code and see how many rows and columns it would select.

As you can see, it has selected only the active cell row, i.e., 1st row and three columns. It is because, for Row Size, we have supplied blank cells, and for Column Size, we have supplied 3. Accordingly, it has selected the data range.

Now, look at the code below.

Code:

Sub Resize_Example()

  Range("A1").Resize(3).Select

End Sub

What this code will do is it will select only three rows, including the active cell row but no extra columns.

Example #3

Use resize to select unknown ranges. Resize is best utilized when you want to select an unknown range of cells. For example, look at the below image of the data range.

It has data from column A to column P. So, we have up until the 700th row.

Assume you know your data will keep changing. You want to select the data range now and then by manually changing the row and column number. However, we can do this easily by using the VBA RESIZE property.

Look at the below code.

Code:

Sub Resize_Example1()

  Dim LR As Long
  Dim LC As Long

  Worksheets("Sales Data").Select

  LR = Cells(Rows.Count, 1).End(xlUp).Row
  LC = Cells(1, Columns.Count).End(xlToLeft).Column

  Cells(1, 1).Resize(LR, LC).Select

End Sub

First, we have declared two variables to find the last used row (LR) and the last used column (LC).

Dim LR As Long
Dim LC As Long

Since our data is in the "Sales Data" worksheet, we choose this worksheet using the code below.

Worksheets("Sales Data").Select

The code below will find the last used row and last used column.

LR = Cells(Rows.Count, 1).End(xlUp).Row

LC = Cells(1, Columns.Count).End(xlToLeft).Column

Now, from the first cell, we are resizing the range from the last used row to the last used column and selecting the method used. So, now it does not matter how big your data is. It will dynamically select the data by finding the last used row and last used column.

Things to Remember

The Resize property in VBA will change the range size from the active cell (including the active cell).
We must provide how many rows and columns to resize from the active cell in VBA.
We cannot use negative row and column numbers for RESIZE property.