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.

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

Syntax of VBA Resize Property

Below is the syntax of the VBA RESIZE property.

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 referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more 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.

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.

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.

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.

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 codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

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

Sub Resize_Example()

Range(“A1”).Resize(0, 3).Select

End Sub

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.

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.

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.

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

  • VBA CLngCDBL VBAVBA Delete SheetVBA UnProtect SheetVBA Format Date