VBA Resize
Last Updated :
21 Aug, 2024
Blog Author :
N/A
Edited by :
Ashish Kumar Srivastav
Reviewed by :
Dheeraj Vaidya
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.
Table of contents
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 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.
Recommended Articles
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: -