Trending October 2023 # How To Create Excel Vba Inputboxw With Examples? # Suggested November 2023 # Top 12 Popular

You are reading the article How To Create Excel Vba Inputboxw With Examples? updated in October 2023 on the website Benhvienthammyvienaau.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Create Excel Vba Inputboxw With Examples?

Excel VBA InputBox

Most of the time, you use the data that is already with you; sometimes, you come up with a situation where you want the user to input the data like Name, Age, etc., kind of personal information. These kinds of input information are needed sometimes when we are conducting a survey and need to review the unbiased opinion of people.

Using Excel VBA’s InputBox, we can get the input data from the user. As the name suggests, InputBox works as a pop-up box which asks the user to feed certain information.

Watch our Demo Courses and Videos

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

The syntax for VBA InputBox

Following is the syntax for VBA InputBox:

Where,

Prompt – Message that gets displayed to the user. This is the only required argument; the rest other arguments are optional.

Title – The heading appears on the dialog window after the successful execution of the InputBox statement. If not given, by default system prints ‘Microsoft Excel’ as a title.

Default – It is the default value that comes on the dialog box. We can keep it null as well. No default value set for this argument.

XPos – Position coordinate of a dialog box on the X-axis.

YPos – Position coordinate of a dialog box on the Y-axis.

HelpFile – Location of the help file that should be used. This argument becomes mandatory to set when the ‘Context’ argument is passed.

Context – Represents Help ContextId for the HelpFile used. Mandatory to use when ‘HelpFile’ argument is passed.

Out of all these arguments, only the first three are enough to successfully create an InputBox.

Creating InputBox in Excel VBA

Below are the different steps to create InputBox in Excel using VBA Code.

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

Create a macro in this module named ‘Module1’. Assign a name to the macro.

Type command InputBox in the editor.

Give the following inputs to the InputBox statement:

Prompt: “May I Know Your Full Name?”

Title: “Personal Information”

Default: “Start Typing Here

Code:

Sub

InputBoxEX() InputBox "May I know Your Name?", "Personal Information", "Start Typing Here"

End Sub

Press F5 or run the button to run this code.

How to Store the Output of InputBox to Cells?

You have created the InputBox to get the input from the user. However, where the output will get stored? We haven’t mentioned any location where the output can be stored.

Let’s store the output we get from InputBox to excel cells by following the below steps in VBA:

Declare a new variable ‘Name’ with type as ‘Variant’. This type of variable can take any value (numeric/string/logical etc.).

Code:

Sub

InputBoxEX()

Dim

Name

As Variant

End Sub

Use InputBox to assign a value to this variable called ‘Name’.

Code:

Sub

InputBoxEX()

Dim

Name

As Variant

Name = InputBox("May I know Your Name?", "Personal Information", "Start Typing Here")

End Sub

If you could have noticed the parenthesis after the InputBox statement, those are needed because we are using this statement for a variable ‘Name’. As soon as it becomes a value to be given for a variable, it has to be mentioned in a proper format with parenthesis.

Now whatever value the user has typed in the dialog box, we want that to see in cell A1 of the Excel sheet. Put the following statement for the same in VBE: Range(“A1”).Value = Name.

Code:

Sub

InputBoxEX()

Dim

Name

As Variant

Name = InputBox("May I know Your Name?", "Personal Information", "Start Typing Here") Range("A1").Value = Name

End Sub

This is it; now, let’s run this code and see how it works.

Any value can be stored using InputBox if the variable is properly defined. In this case, you have defined the variable ‘Name’ as ‘Variant’. The variant data type can take any data value, as I said earlier.

See the example below:

I am giving a number as an argument to dialog box when it pops up. See as below:

The value in cell A1 is changed to 2023.

Now let’s change the variable type to Date.

Code:

Sub

InputBoxEX()

Dim

Name

As Date

Name = InputBox("May I know Your Name?", "Personal Information", "Start Typing Here") Range("A1").Value = Name

End Sub

It occurred because the type of variable name is Date now, and I have given an input argument other than date value (a string name). Due to which this code doesn’t execute and throws an error.

Validation of User Input

What if I tell you that the user input can be restricted? Yes, It’s true! You can restrict the user input to Characters, Numbers or Logical, etc.

To restrict the user input, you can use the Application.InputBox.

The syntax for Application.InputBox is as follows:

Where,

Prompt – Message that gets popped up for the user.

Title – Heading of the dialog box.

Default – Default value that pops up on typing area under dialog box.

Type – The type of input.

Let’s start this through example.

Declare a variable Name as a Variant.

Code:

Sub

InputBoxEX()

Dim

Name

As Variant

End Sub

Assign Application.InputBox to the variable called Name with the same arguments as those you have used InputBox. i.e. Prompt, Title and Default. See the code below:

Code:

Sub

InputBoxEX()

Dim

Name

As Variant

Name=Application.InputBox("May I know Your Name?","Personal Information","Start Typing Here")

End Sub

Now, put comma 5 times to ignore the Left, Top, HelpFile and HelpContextID. After 5 commas, you can specify the input type.

Code:

Sub

InputBoxEX()

Dim

Name

As Variant

Name = Application.InputBox("May I know Your Name?","Personal Information","Start Typing Here",,,,,

End Sub

Type of the input string have below mentioned validations:

Let’s choose 1 as a Type in our statement. It means only Numbers/Numeric Values are acceptable in the dialog box which pops up.

Code:

Sub

InputBoxEX()

Dim

Name

As Variant

Name = Application.InputBox("May I know Your Name?","Personal Information","Start Typing Here",,,,,1)

End Sub

It says the number is not valid. That seems logical because we have set the variable input type as the number and provide the text as an input that is not acceptable.

In this way, you can restrict the user to enter only the values which you want to see through InputBox.

Things to Remember

InputBox accepts up to 255 arguments and can display only 254. So be cautious about the max length a user can enter.

The application method can be used to set the input data type. However, if it is not used, then you need to be more specific about the input data type.

A variant data type is recommended to choose as it can hold any of Numeric/Text/Logical etc. B=Values.

Excel Vba Function Tutorial: Return, Call, Examples

What is a Function?

A function is a piece of code that performs a specific task and returns a result. Functions are mostly used to carry out repetitive tasks such as formatting data for output, performing calculations, etc.

Suppose you are developing a program that calculates interest on a loan. You can create a function that accepts the loan amount and the payback period. The function can then use the loan amount and payback period to calculate the interest and return the value.

Why use functions

Rules of naming functions

The rules for naming functions as the same as the ones in the above section on rules for naming subroutines.

VBA Syntax for declaring Function Private Function myFunction (ByVal arg1 As Integer, ByVal arg2 As Integer) myFunction = arg1 + arg2 End Function

HERE in the syntax,

Code Action

“Private Function myFunction(…)”

Here the keyword “Function” is used to declare a function named “myFunction” and start the body of the function.

The keyword ‘Private’ is used to specify the scope of the function

“ByVal arg1 As Integer, ByVal arg2 As Integer”

It declares two parameters of integer data type named ‘arg1’ and ‘arg2.’

myFunction = arg1 + arg2

evaluates the expression arg1 + arg2 and assigns the result to the name of the function.

“End Function”

“End Sub” is used to end the body of the function

Function demonstrated with Example:

Functions are very similar to the subroutine. The major difference between a subroutine and a function is that the function returns a value when it is called. While a subroutine does not return a value, when it is called. Let’s say you want to add two numbers. You can create a function that accepts two numbers and returns the sum of the numbers.

Create the user interface

Add the function

Write code for the command button

Test the code

Step 1) User interface

Add a command button to the worksheet as shown below

Set the following properties of CommanButton1 to the following.

S/N Control Property Value

1 CommandButton1 Name btnAddNumbers

Caption Add Numbers Function

Your interface should now appear as follows

Step 2) Function code.

Press Alt + F11 to open the code window

Add the following code

Private Function addNumbers(ByVal firstNumber As Integer, ByVal secondNumber As Integer) addNumbers = firstNumber + secondNumber End Function

HERE in the code,

Code Action

“Private Function addNumbers(…)”

It declares a private function “addNumbers” that accepts two integer parameters.

“ByVal firstNumber As Integer, ByVal secondNumber As Integer”

It declares two parameter variables firstNumber and secondNumber

“addNumbers = firstNumber + secondNumber”

It adds the firstNumber and secondNumber values and assigns the sum to addNumbers.

Step 3) Write Code that calls the function

Select View Code

Add the following code

MsgBox addNumbers(2, 3) End Sub

HERE in the code,

Code Action

“MsgBox addNumbers(2,3)”

It calls the function addNumbers and passes in 2 and 3 as the parameters. The function returns the sum of the two numbers five (5)

Step 4) Run the program, you will get the following results

Download Excel containing above code

Download the above Excel Code

Summary:

A function is a piece of code that performs a specific task. A function returns a value after execution.

Both subroutines and functions offer code reusability

Both subroutines and functions help break down large chunks of code into small manageable code.

Merge Excel Worksheets With Vba

The Scenario

Let’s say that you have a lot of sheets in your workbook and you want to merge all the data onto a single worksheet.

If you have your data laid out in the same way on each sheet then this piece of VBA will do the trick for you. Maybe you have sales reports for different regions/products/salespeople on separate sheets, something like this :

Sheet 1 Sheet 2

The code allows for a header row (which is taken from the first sheet), and just copies the data off the remaining sheets.

Merging the sheets together will give you this :

Note : I used Excel’s RANDBETWEEN function to generate the sales figures, arter than type them out by hand.

The merged data is copied onto a sheet called MergedData. If you want to change this sheet name, just change the value between the double quotes of the MergeSheetName variable in the VBA as shown here :

When you run the code this is what happens :

A new sheet called MergedData is created (or whatever you want to call it). If this sheet already exists then all data on it is deleted

The header row and data from the first sheet in the workbook is copied to the merged data sheet

The data from all other sheets is copied to the merged data sheet

The header row on the merged results is made BOLD

The columns on the merged results are auto-fitted

What to Expect from the VBA

There are certain things the code does not do and it’s important to understand these so you don’t end up with unexpected results

The range to be copied must be contiguous. I use a VBA property called CurrentRegion which copies a range bounded by blank rows/columns like so :

Our active cell is B2 so the range to copy is A2 to F4. CurrentRegion will select the range starting at A1, but I’m resizing the range in VBA to exclude the first row as we’ve already copied that from the first sheet. We only want the header row once.

Row 6 and Column H are ignored as Row 5 and Column G are blank – these are where CurrentRegion understands the range ends.

You can read Microsoft’s explanation of CurrentRegion here

This code cannot be used on a protected worksheet, CurrentRegion does not support this.

The code pastes values and formatting. So your merged sheet will not contain any formulas

Where’s the Code?

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the workbook and open the VBA editor (ALT+F11) to check out the code and start merging.

What do you think?

If you need a hand adapting this to do something else for you please let me know.

This code was written as a result of a question from one of our students, Anna Reifman, thanks Anna.

I’d like to hear from you if you have a problem you think VBA could fix, or if you have your own solution to merging sheets, or even if you have adapted my code to do something else.

How To Sort Worksheets In Excel Using Vba (Alphabetically)

If you work with a lot of worksheets in Excel, you would know that management of it can become an issue.

Once you have more than a couple of worksheets, you need to manually arrange these.

How easy would it be had there been a way to quickly sort the worksheets alphabetically in Excel.

While there is no inbuilt feature way to do this, it can be done (easily) using VBA.

In this tutorial, I will give you the code and the exact steps you need to follow to sort worksheets in Excel.

You can tweak the code to sort the worksheets in an ascending or descending order.

Below is the code that will sort the worksheets in alphabetical order as soon as you run it.

Sub SortWorksheetsTabs() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

The above is a simple code that uses to For Next loops to analyze each worksheet against all the worksheets.

It compares the name of a worksheet against all the worksheets and moves it based on its name in alphabetical order.

It then moves on to the next worksheet and then checks it against all the worksheets.

This process is repeated for all the worksheets and the final result is an order of worksheets sorted in alphabetical order.

A few important things to know about this code:

UCase function is used to make sure that the lowercase and uppercase are not treated differently.

The value of Application.ScreenUpdating is set to False at the beginning of the code and changed to True at the end of the code. This ensures that while the code is running, you don’t see it happening on the screen. This also helps speed up the code execution.

The below code would sort the worksheets in descending order:

'This code will sort the worksheets alphabetically Sub SortWorksheetsTabs() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

You can also give the user the option to choose whether he/she wants to sort in ascending/descending order.

The below code would show a message box and the user can select the order to sort.

Sub SortWorksheetsTabs() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer Dim SortOrder As VbMsgBoxResult SortOrder = MsgBox("Select Yes for Ascending Order and No for Descending Order", vbYesNoCancel) ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If SortOrder = vbYes Then If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then Sheets(j).Move before:=Sheets(i) End If ElseIf SortOrder = vbNo Then Sheets(j).Move before:=Sheets(i) End If End If Next j Next i Application.ScreenUpdating = True End Sub

The above code when executed shows a message as shown below. It sorts based on the selection (Yes for Ascending and No for Descending).

Note: The sorting cannot be undone. In case you want to keep the original order as well, make a copy of the workbook.

A word of caution: The above code works in most cases. One area where it will give you the wrong result is when you have tab names such as Q1 2023, Q2 2023, Q1 2023, and Q2 2023. Ideally, you would want all the tabs for the same years to be together, but it won’t be done as Q1 2023 will be placed before Q2 2023.

Excel has a VBA backend called the VBA editor.

You need to copy and paste the VBA code into the VB Editor module code window.

Here are the steps to do this:

Copy and paste the code in the module window.

In Excel, there are various ways to run the VBA code.

You can run the code right from the Visual Basic Editor (also called the VB Editor).

You can read all about running the macro here - How to Run a Macro in Excel (or watch the video below).

You May Also Like the Following Excel/VBA Tutorials:

How To Create Payroll In Excel? (Step

What is Payroll in Excel?

Companies use Payroll in Excel to perform pay-related calculations like in-hand salary, deductions, and taxes for their employees using Microsoft Excel.

Managing payroll manually can take up a lot of time and resources. To simplify the process, many companies rely on software like Tally or ADP or even outsource payroll to external service providers. However, some businesses prefer to manage payroll on their own because it gives them complete control over their employees’ pay and expenses.

So, companies that want to create payroll internally need a powerful, flexible, user-friendly, and easy-to-use tool. The perfect tool that fulfills all these requirements is Microsoft Excel. It has a vast range of formulas and a very simple layout, making it easy to use.

This article will show you the step-by-step process of how to create payroll in Excel using a very simple example.

Table of Contents Example: How to Make Payroll in Excel? (Stepwise)

Let’s understand how to make Payroll in Excel with a few steps.

We have provided a downloadable payroll template in Excel for your better understanding.

The first step in making payroll in Excel is to open a new Excel sheet. For this:

Go to the “Search Box” at the bottom-left end of the Windows desktop screen.

Type “Excel”

Note: We have included screenshots of Excel for each step, making it simple for you to follow along with the process.

Step #2: Save the File

Save the file in a preferred location so you can easily access the file whenever you need.

Step #3: Add Columns

Next, add some columns in the sheet to add data like employee name, pay/hour, total hours worked, etc., for the payroll calculation. Enter the column names in the following hierarchy:

Sr. No.

Payroll Parameters

Add in Column

Details

1 Employee Name A Contains the employee names.

2 Pay/Hour B Total money an employee earns for one hour of work.

3 Total Hours Worked C Total hours the employee worked in a month or a particular pay period.

4 Overtime Pay/Hour D Pay an employee earns for working beyond the regular working hours.

5 Total Overtime Hours E Total hours the employee worked overtime in a month or a particular pay period.

6 Gross Pay F The amount you have to pay the employee (before deductions)

7 Income Tax G Tax payable on Gross pay.

8 (If Any) H Deductibles other than income tax like TDS, EPF, PT, etc.

9 Net Pay I In-hand salary of the employee.

The above data in Excel will look like this:

Step #4: Enter Employee Data

After creating columns, enter the necessary details in the appropriate columns like the employee’s name in column A, the total no. of hours worked by an employee in column C, and so on.

Note: Total Hours Worked and Total Overtime Hours data is for a month. Moreover, Pay/Hour and Overtime/Hour are in USD.

Step #5: Calculate Gross Pay

Now, we will calculate the Gross pay in column F. We will use the following formula to calculate gross pay.

Gross Pay = (Pay per Hour x Total Hours Worked) + (Overtime Pay per Hour x Total Overtime Hours)

=(B2*C2)+(D2*E2)

Select “Cell F2” and enter the above formula.

Press “Enter”. Excel will calculate the gross salary and display 3500 in Cell F2.

Similarly, to calculate the gross pay for all employees, drag the cell downwards to apply the same formula to Cell F3 to Cell F6.

Step #6: Find Income Tax Amount

Gross pay is always the base for calculating Income Tax. To calculate the Income Tax, you must know the tax percentage your employee pays on their total gross pay. In this example, let’s assume only one tax rate of 15% for all employees.

Note: There are different

There are different tax slabs depending on different salary brackets. You must know the specific percentage of income tax that applies to each employee of your company.

The formula to calculate Income Tax in payroll is as follows:

Income Tax = (Gross Pay x Income Tax percentage)

=F2 * 0.15

Select “Cell G2” and enter the above formula.

Press “Enter”.

The formula will calculate and display the income tax as 525 in Cell G2.

Drag the cell downwards from Cell G2 to Cell G6 to apply the formula for other employees.

Step #7: Subtract Deductions

Mention all the deductions related to your employees apart from income tax in column H. It includes all deductions like Health/Life insurance, EMI on loans, Employee Provident Fund, Professional tax, etc. You must also mention any other deductibles for your employees, and if there are no other deductibles, you can set the value to zero.

Step #8: Calculate Net Pay

Net pay is the final amount the company pays the employees after all deductions from the gross pay. It is also known as in-hand salary.

The formula to calculate Net Pay in payroll is as follows:

Net Pay = Gross Pay – (Income tax + Other Deductibles)

So, the formula in Excel will become,

=F2-(G2+H2)

Select “Cell I2” and enter the above formula.

Press “Enter”.

The formula calculates the net pay and displays 2775 in Cell I2, as shown below.

Drag the cell downwards to apply the formula to the rest of the cells.

Step #9: Calculate the Sum of Payroll Parameters

Finally, let’s calculate the sum of all the important parameters of this payroll dataset.

Let us start by calculating the sum of the total hours that all employees worked.

For this, enter the below formula in “Cell C7”.

=SUM(C2:C7

Press “Enter”. The formula calculates the sum and displays 763 in Cell C8.

Repeat the above steps for total overtime hours, gross pay, income tax, other deductibles, and net pay.

The formula to calculate the sum of all the above parameters are as follows:

Total Overtime Hours: =SUM(E2:E6)

Gross Pay: =SUM(F2:F6)

Income Tax: =SUM(G2:G6)

Other Deductibles: =SUM(H2:H6)

Net Pay: =SUM(I2:I6)

We have successfully created a basic Payroll layout in Excel. You can customize this template according to your requirements.

At the end of each month, HR has to provide every employee with a salary slip that includes their base pay, overtime pay, deductions, gross income, final pay, and more. Thus, after successfully generating the payroll, the HR department can simply use the calculated details to create a detailed salary slip or statement for each employee.

Things to Remember About Payroll in Excel

Although there are several tools that you can use for creating and managing payroll, using Excel is more secure as you have direct control over every data.

We have only provided a basic payroll layout. If required, you can add more columns, like health insurance premiums, life cover premiums, etc.

Frequently Asked Questions (FAQs)

Answer: The most common types of payroll cycles are weekly, every two weeks (bi-weekly), twice a month (semi-monthly), and monthly. Different organizations use different payroll cycles, and also it varies between countries.

Answer: There are four basic steps in calculating payroll, which are as follows:

Create a Payroll table in Excel with all required columns

Add employee information

Calculate gross pay, income tax, and total deductibles

Determine the net pay for all employees

Finally, create a salary slip for each employee.

Answer: Payroll software is a computer application that automatically calculates and manages employee salaries. There are various paid software, like Gusto, ADP, QuickBooks, Justworks, etc. There are also free options, like OnPay, Zoho Payroll, and HR.my.

Working With Cells And Ranges In Excel Vba (Select, Copy, Move, Edit)

When working with Excel, most of your time is spent in the worksheet area – dealing with cells and ranges.

And if you want to automate your work in Excel using VBA, you need to know how to work with cells and ranges using VBA.

There are a lot of different things you can do with ranges in VBA (such as select, copy, move, edit, etc.).

So to cover this topic, I will break this tutorial into sections and show you how to work with cells and ranges in Excel VBA using examples.

Let’s get started.

All the codes I mention in this tutorial need to be placed in the VB Editor . Go to the ‘ Where to Put the VBA Code ‘ section to know how it works.

If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training.

To work with cells and ranges in Excel using VBA, you don’t need to select it.

In most of the cases, you are better off not selecting cells or ranges (as we will see).

Despite that, it’s important you go through this section and understand how it works. This will be crucial in your VBA learning and a lot of concepts covered here will be used throughout this tutorial.

So let’s start with a very simple example.

If you want to select a single cell in the active sheet (say A1), then you can use the below code:

Sub SelectCell() Range("A1").Select End Sub

The above code has the mandatory ‘Sub’ and ‘End Sub’ part, and a line of code that selects cell A1.

Range(“A1”) tells VBA the address of the cell that we want to refer to.

Select is a method of the Range object and selects the cells/range specified in the Range object. The cell references need to be enclosed in double quotes.

This code would show an error in case a chart sheet is an active sheet. A chart sheet contains charts and is not widely used. Since it doesn’t have cells/ranges in it, the above code can’t select it and would end up showing an error.

Note that since you want to select the cell in the active sheet, you just need to specify the cell address.

But if you want to select the cell in another sheet (let’s say Sheet2), you need to first activate Sheet2 and then select the cell in it.

Sub SelectCell() Worksheets("Sheet2").Activate Range("A1").Select End Sub

Similarly, you can also activate a workbook, then activate a specific worksheet in it, and then select a cell.

Sub SelectCell() Workbooks("Book2.xlsx").Worksheets("Sheet2").Activate Range("A1").Select End Sub

Note that when you refer to workbooks, you need to use the full name along with the file extension (.xlsx in the above code). In case the workbook has never been saved, you don’t need to use the file extension.

Now, these examples are not very useful, but you will see later in this tutorial how we can use the same concepts to copy and paste cells in Excel (using VBA).

Just as we select a cell, we can also select a range.

In case of a range, it could be a fixed size range or a variable size range.

In a fixed size range, you would know how big the range is and you can use the exact size in your VBA code. But with a variable-sized range, you have no idea how big the range is and you need to use a little bit of VBA magic.

Let’s see how to do this.

Here is the code that will select the range A1:D20.

Sub SelectRange() Range("A1:D20").Select End Sub

Another way of doing this is using the below code:

Sub SelectRange() Range("A1", "D20").Select End Sub

The above code takes the top-left cell address (A1) and the bottom-right cell address (D20) and selects the entire range. This technique becomes useful when you’re working with variably sized ranges (as we will see when the End property is covered later in this tutorial).

If you want the selection to happen in a different workbook or a different worksheet, then you need to tell VBA the exact names of these objects.

For example, the below code would select the range A1:D20 in Sheet2 worksheet in the Book2 workbook.

Sub SelectRange() Workbooks("Book2.xlsx").Worksheets("Sheet1").Activate Range("A1:D20").Select End Sub

Now, what if you don’t know how many rows are there. What if you want to select all the cells that have a value in it.

In these cases, you need to use the methods shown in the next section (on selecting variably sized range).

There are different ways you can select a range of cells. The method you choose would depend on how the data is structured.

In this section, I will cover some useful techniques that are really useful when you work with ranges in VBA.

In cases where you don’t know how many rows/columns have the data, you can use the CurrentRange property of the Range object.

The CurrentRange property covers all the contiguous filled cells in a data range.

Below is the code that will select the current region that holds cell A1.

Sub SelectCurrentRegion() Range("A1").CurrentRegion.Select End Sub

The above method is good when you have all data as a table without any blank rows/columns in it.

But in case you have blank rows/columns in your data, it will not select the ones after the blank rows/columns. In the image below, the CurrentRegion code selects data till row 10 as row 11 is blank.

In such cases, you may want to use the UsedRange property of the Worksheet Object.

UsedRange allows you to refer to any cells that have been changed.

So the below code would select all the used cells in the active sheet.

Sub SelectUsedRegion() ActiveSheet.UsedRange.Select End Sub

Note that in case you have a far-off cell that has been used, it would be considered by the above code and all the cells till that used cell would be selected.

Now, this part is really useful.

The End property allows you to select the last filled cell. This allows you to mimic the effect of Control Down/Up arrow key or Control Right/Left keys.

Let’s try and understand this using an example.

Suppose you have a dataset as shown below and you want to quickly select the last filled cells in column A.

The problem here is that data can change and you don’t know how many cells are filled. If you have to do this using keyboard, you can select cell A1, and then use Control + Down arrow key, and it will select the last filled cell in the column.

Now let’s see how to do this using VBA. This technique comes in handy when you want to quickly jump to the last filled cell in a variably-sized column

Sub GoToLastFilledCell() Range("A1").End(xlDown).Select End Sub

The above code would jump to the last filled cell in column A.

Similarly, you can use the End(xlToRight) to jump to the last filled cell in a row.

Sub GoToLastFilledCell() Range("A1").End(xlToRight).Select End Sub

Now, what if you want to select the entire column instead of jumping to the last filled cell.

You can do that using the code below:

Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown)).Select End Sub

In the above code, we have used the first and the last reference of the cell that we need to select. No matter how many filled cells are there, the above code will select all.

Remember the example above where we selected the range A1:D20 by using the following line of code:

Range(“A1″,”D20”)

Here A1 was the top-left cell and D20 was the bottom-right cell in the range. We can use the same logic in selecting variably sized ranges. But since we don’t know the exact address of the bottom-right cell, we used the End property to get it.

In Range(“A1”, Range(“A1”).End(xlDown)), “A1” refers to the first cell and Range(“A1”).End(xlDown) refers to the last cell. Since we have provided both the references, the Select method selects all the cells between these two references.

Similarly, you can also select an entire data set that has multiple rows and columns.

The below code would select all the filled rows/columns starting from cell A1.

Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select End Sub

In the above code, we have used Range(“A1”).End(xlDown).End(xlToRight) to get the reference of the bottom-right filled cell of the dataset.

If you’re wondering why use the End property to select the filled range when we have the CurrentRegion property, let me tell you the difference.

With End property, you can specify the start cell. For example, if you have your data in A1:D20, but the first row are headers, you can use the End property to select the data without the headers (using the code below).

Sub SelectFilledCells() Range("A2", Range("A2").End(xlDown).End(xlToRight)).Select End Sub

But the CurrentRegion would automatically select the entire dataset, including the headers.

So far in this tutorial, we have seen how to refer to a range of cells using different ways.

Now let’s see some ways where we can actually use these techniques to get some work done.

As I mentioned at the beginning of this tutorial, selecting a cell is not necessary to perform actions on it. You will see in this section how to copy cells and ranges without even selecting these.

Let’s start with a simple example.

If you want to copy cell A1 and paste it into cell D1, the below code would do it.

Sub CopyCell() Range("A1").Copy Range("D1") End Sub

Note that the copy method of the range object copies the cell (just like Control +C) and pastes it in the specified destination.

In the above example code, the destination is specified in the same line where you use the Copy method. If you want to make your code even more readable, you can use the below code:

Sub CopyCell() Range("A1").Copy Destination:=Range("D1") End Sub

The above codes will copy and paste the value as well as formatting/formulas in it.

As you might have already noticed, the above code copies the cell without selecting it. No matter where you’re on the worksheet, the code will copy cell A1 and paste it on D1.

Also, note that the above code would overwrite any existing code in cell D2. If you want Excel to let you know if there is already something in cell D1 without overwriting it, you can use the code below.

Sub CopyCell() Response = MsgBox("Do you want to overwrite the existing data", vbYesNo) End If If Response = vbYes Then Range("A1").Copy Range("D1") End If End Sub

If you want to copy A1:D20 in J1:M20, you can use the below code:

Sub CopyRange() Range("A1:D20").Copy Range("J1") End Sub

In the destination cell, you just need to specify the address of the top-left cell. The code would automatically copy the exact copied range into the destination.

You can use the same construct to copy data from one sheet to the other.

The below code would copy A1:D20 from the active sheet to Sheet2.

Sub CopyRange() Range("A1:D20").Copy Worksheets("Sheet2").Range("A1") End Sub

The above copies the data from the active sheet. So make sure the sheet that has the data is the active sheet before running the code. To be safe, you can also specify the worksheet’s name while copying the data.

Sub CopyRange() Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1") End Sub

The good thing about the above code is that no matter which sheet is active, it will always copy the data from Sheet1 and paste it in Sheet2.

You can also copy a named range by using its name instead of the reference.

For example, if you have a named range called ‘SalesData’, you can use the below code to copy this data to Sheet2.

Sub CopyRange() Range("SalesData").Copy Worksheets("Sheet2").Range("A1") End Sub

If the scope of the named range is the entire workbook, you don’t need to be on the sheet that has the named range to run this code. Since the named range is scoped for the workbook, you can access it from any sheet using this code.

If you have a table with the name Table1, you can use the below code to copy it to Sheet2.

Sub CopyTable() Range("Table1[#All]").Copy Worksheets("Sheet2").Range("A1") End Sub

You can also copy a range to another Workbook.

In the following example, I copy the Excel table (Table1), into the Book2 workbook.

Sub CopyCurrentRegion() Range("Table1[#All]").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1") End Sub

This code would work only if the Workbook is already open.

One way to copy variable sized ranges is to convert these into named ranges or Excel Table and the use the codes as shown in the previous section.

But if you can’t do that, you can use the CurrentRegion or the End property of the range object.

The below code would copy the current region in the active sheet and paste it in Sheet2.

Sub CopyCurrentRegion() Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1") End Sub

If you want to copy the first column of your data set till the last filled cell and paste it in Sheet2, you can use the below code:

Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1") End Sub

If you want to copy the rows as well as columns, you can use the below code:

Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1") End Sub

Note that all these codes don’t select the cells while getting executed. In general, you will find only a handful of cases where you actually need to select a cell/range before working on it.

So far, we have been using the full address of the cells (such as Workbooks(“Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”)).

To make your code more manageable, you can assign these ranges to object variables and then use those variables.

For example, in the below code, I have assigned the source and destination range to object variables and then used these variables to copy data from one range to the other.

Sub CopyRange() Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Worksheets("Sheet1").Range("A1:D20") Set DestinationRange = Worksheets("Sheet2").Range("A1") SourceRange.Copy DestinationRange End Sub

We start by declaring the variables as Range objects. Then we assign the range to these variables using the Set statement. Once the range has been assigned to the variable, you can simply use the variable.

You can use the Input boxes to allow the user to enter the data.

For example, suppose you have the data set below and you want to enter the sales record, you can use the input box in VBA. Using a code, we can make sure that it fills the data in the next blank row.

Sub EnterData() Dim RefRange As Range Set RefRange = Range("A1").End(xlDown).Offset(1, 0) Set ProductCategory = RefRange.Offset(0, 1) Set Quantity = RefRange.Offset(0, 2) Set Amount = RefRange.Offset(0, 3) RefRange.Value = RefRange.Offset(-1, 0).Value + 1 ProductCategory.Value = InputBox("Product Category") Quantity.Value = InputBox("Quantity") Amount.Value = InputBox("Amount") End Sub

The above code uses the VBA Input box to get the inputs from the user, and then enters the inputs into the specified cells.

Note that we didn’t use exact cell references. Instead, we have used the End and Offset property to find the last empty cell and fill the data in it.

This code is far from usable. For example, if you enter a text string when the input box asks for quantity or amount, you will notice that Excel allows it. You can use an If condition to check whether the value is numeric or not and then allow it accordingly.

So far we can have seen how to select, copy, and enter the data in cells and ranges.

In this section, we will see how to loop through a set of cells/rows/columns in a range. This could be useful when you want to analyze each cell and perform some action based on it.

For example, if you want to highlight every third row in the selection, then you need to loop through and check for the row number. Similarly, if you want to highlight all the negative cells by changing the font color to red, you need to loop through and analyze each cell’s value.

Here is the code that will loop through the rows in the selected cells and highlight alternate rows.

Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Each Myrow In Myrange.Rows If chúng tôi Mod 2 = 0 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub

The above code uses the MOD function to check the row number in the selection. If the row number is even, it gets highlighted in cyan color.

Here is another example where the code goes through each cell and highlights the cells that have a negative value in it.

Sub HighlightAlternateRows() Dim Myrange As Range Dim Mycell As Range Set Myrange = Selection For Each Mycell In Myrange If Mycell < 0 Then Mycell.Interior.Color = vbRed End If Next Mycell End Sub

Note that you can do the same thing using Conditional Formatting (which is dynamic and a better way to do this). This example is only for the purpose of showing you how looping works with cells and ranges in VBA.

Wondering where the VBA code goes in your Excel workbook?

Excel has a VBA backend called the VBA editor. You need to copy and paste the code in the VB Editor module code window.

Here are the steps to do this:

Go to the Developer tab.

Copy and paste the code in the module window.

You May Also Like the Following Excel Tutorials:

Update the detailed information about How To Create Excel Vba Inputboxw With Examples? on the Benhvienthammyvienaau.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!