EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Lookup & Reference Functions VBA Resize
Secondary Sidebar
VBA Lookup & Reference Functions
  • VBA Lookup & Reference
    • VBA Selecting Range
    • VBA Range
    • VBA Match
    • VBA Transpose
    • VBA Delete Row
    • VBA Range Cells
    • VBA Delete Column
    • VBA Union
    • VBA Insert Column
    • VBA Named Range
    • VBA Hyperlink
    • VBA Last Row
    • VBA Name
    • VBA OFFSET
    • VBA Hide Columns
    • VBA Selection Range
    • VBA Columns
    • VBA Insert Row
    • VBA LOOKUP
    • VBA VLOOKUP Function
    • VBA Resize
    • Compare Two Columns in Excel using VLOOKUP
    • VLOOKUP with Different Sheets

VBA Resize

By Madhuri ThakurMadhuri Thakur

VBA Resize

Excel VBA Resize

VBA Resize is a method where we use it to highlight the cells by changing their size for visualization. The important thing which needs to be noted is that this method is used only for illustration purposes. Resize is a property that is used with the range property method to display the selection of rows and columns provided as an argument with a reference to a given range.

Syntax of VBA Resize:

The syntax for this property is as follows:

VBA Resize Syntax

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Row size is the number of rows we want to select or highlight and column size is the number of columns we want to select and highlight. This property needs a reference to a range. If one of the arguments from row size or column size is not provided with this function selects the entire row or the column from the range. The methods to use Resize property in VBA is as follows:

  1. First, we need to provide a range for reference.
  2. Next step is to provide the arguments for the rows and size for resize property.
  3. To illustrate it we need to use the select property method.

Using the Resize Property in Excel VBA

The following examples will teach us how to use Resize Property in Excel by using the VBA Code.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,641 ratings)
You can download this VBA Resize Excel Template here – VBA Resize Excel Template

Example #1

Let us first begin with the basic resize property. In this example, we will see how to use the resize the property and how we need to provide the inputs for this resize function in general. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Insert Module

Step 2: Now write the subprocedure for VBA Resize.

Code:

Sub Example1()

End Sub

VBA Resize Example1-2

Step 3: Resize is a range property of VBA let us select a range.

Code:

Sub Example1()

Range("A1")

End Sub

Range property Example 1-3

Step 4: After that, we can use the dot operator and use the resize method to select the number of columns and rows.

Code:

Sub Example1()

Range("A1").Resize(RowSize:=2, ColumnSize:=2)

End Sub

Dot Operator Example 1-4

Step 5: Then we can use the select method property for illustration purposes.

Code:

Sub Example1()

Range("A1").Resize(RowSize:=2, ColumnSize:=2).Select

End Sub

VBA Resize Example 1-5

Step 6: Run the Code by hitting F5 or the Run button. and see the result in worksheet 1.

VBA Resize Example 1-5

We have selected two rows and two columns to the range.

Example #2

In the above example, we have used the same number of rows and columns for the range. Let us try a different approach and use different selection such as row size to be 3 and column size to be 2. For this, follow the below steps:

Step 1: We can use the same module and begin with our subprocedure for the second example.

Code:

Sub Example2()

End Sub

Example 2 - Step 1

Step 2: Now as this is a range property we will use the range method to reference a cell.

Code:

Sub Example2()

Range("A1:C4")

End Sub

Range Property Example 2-2

Step 3: Then we will use the resize method and select the row and column size for the arguments.

Code:

Sub Example2()

Range("A1:C4").Resize(3, 2)

End Sub

VBA Resize Example 2-3

Step 4: And the final step is to use the Select property method for the illustration.

Code:

Sub Example2()

Range("A1:C4").Resize(3, 2).Select

End Sub

VBA Resize Example 2-4

Step 5: Now when we execute the above code by hitting F5 .we can see the following result in sheet 1.

sheet 1 Example2-5

Out of the range A1:C4 this code has selected three rows and two columns.

Example #3

So in the above examples, we saw how to resize property works if there is same number of rows and columns or a different number of rows and columns as the argument. Now let us see what happens when we do not provide one of the arguments to the function. For this, follow the below steps:

Step 1: Declare another Subprocedure.

Code:

Sub Example3()

End Sub

VBA Resize Example 3-1

Step 2: Now we can select any random range.

Code:

Sub Example3()

Range("A1:C4")

End Sub

Random Range Example 3-4

Step 3: Now we will use the resize property but we will remove the row specification from the code.

Code:

Sub Example3()

Range("A1:C4").Resize(, 1)

End Sub

VBA Resize Example 3-2

Step 4: Now we will use the select method.

Code:

Sub Example3()

Range("A1:C4").Resize(, 1).Select

End Sub

Example 3 - Step 4

Step 5: Run the Code by hitting F5 or the Run button.

VBA Resize Example 3-5

It selected one column but the entire four rows.

Example #4

Now let us use this resize property in a dynamic example. For this example, we will first select some range which will have some number of rows and columns and we will use this resize property to select 2 extra rows and column from the selection. For this, follow the below steps:

Step 1: In the same module let us begin with a subprocedure.

Code:

Sub Example4()

End Sub

VBA Resize Example 4-1

Step 2: First let us activate sheet 1 first using the worksheet property method.

Code:

Sub Example4()

Worksheets("Sheet1").Activate

End Sub

VBA Resize Example 4-2

Step 3: Now let us provide the selection with the number of rows and number of columns using the selection property method as shown below.

Code:

Sub Example4()

Worksheets("Sheet1").Activate
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count

End Sub

VBA Resize Example 4-3

Step 4: Now we can use the resize property to increase the selection by two rows and two columns.

Code:

Sub Example4()

Worksheets("Sheet1").Activate
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 2, numColumns + 2).Select

End Sub

VBA Resize Example 4-4

Step 5: So here is our selection before the execution of the code.

Code:

VBA Resize Example 4-5

Step 6: When we execute the code.

VBA Resize Example 4-6

The following code extended the selection by two rows and two columns.

Things to Remember

There are few things which we need to remember about VBA Resize:

  • This is a range property method.
  • It is used to illustrate the selection of rows and columns from a given range.
  • The first argument in this function is a row reference and the second argument is a range reference.
  • If one of the arguments from row size or column size is not provided this function selects the entire row or the column from the range.

Recommended Articles

This is a guide to the VBA Resize. Here we discuss how to use the Resize property in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA SubString
  2. VBA Dynamic Array
  3. VBA Login
  4. VBA Month
Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)
  120+ Online Courses |  500+ Hours |  Verifiable Certificates |  Lifetime Access
4.9
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more