VBA Resize

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 the cell B5, and from this cell, if you want to select 3 rows and two columns, we can change the size of a range by using the RESIZE property of VBA.

VBA-Resize

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Resize (wallstreetmojo.com)

Syntax of VBA Resize Property

Below is the syntax of the VBA RESIZE property.

Range().Resize([Row Size], [Column Size])

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

Then use excel VBA Resize property, and in this property, we need to supply row size limit and column size limit. Based on the provided row numbers and column numbers, it will resize it.

Examples of using Resize in VBA

Below are the examples of using resize in excel VBA.

You can download this VBA Resize Excel Template here – VBA Resize Excel Template

Example #1

Assume you have data from A1 to B14 cell, and from 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.

VBA Resize Example 1

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

Code:

Sub Resize_Example()

 Range ("A1").

End Sub
VBA Resize Example 1-1

For this range, use RESIZE property.

Code:

Sub Resize_Example()

 Range ("A1").Resize(

End Sub
VBA Resize Example 1-2

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
VBA Resize Example 1-3

The next argument is Column Size for this, enter how columns you need to select. I will enter 3 columns.

Code:

Sub Resize_Example()

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

End Sub
VBA Resize Example 1-4

Once the resizing is done, we need to supply what we need to do with this range. I will just 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 how many columns it would select.

VBA Resize Example 1-5

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, and 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 how many columns it would select.

Example 2

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

Now, look at the below code.

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 2-1

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.

Example 3

It has data all the ways from Column A to Column P and row-wise we have up until the 700th row.

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

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, I 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 worksheet named “Sales Data,” we are choosing this worksheet by using the below code.

Worksheets(“Sales Data”).Select

Now below code 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 select is the method used. So now it doesn’t matter how big your data is. It will dynamically select the data by finding the last used row and last used column.

Example 3-1

Things to Remember

  • Resize property in VBA will change the size of the range from the active cell (including the active cell as well).
  • We just need to provide how many rows and how many columns to be resized from the active cell in VBA.
  • We cannot use negative row & column number for RESIZE property.

Recommended Articles

This has been a guide to VBA Resize. Here we discuss examples of resize in excel VBA using the RESIZE property of VBA and downloadable excel templates. Below are some useful articles related to VBA –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>