Vba
VBA Logical Operators: AND, OR, NOT, IF NOT in Excel VBA
VBA Logical Operators: AND, OR, NOT Excel VBA Logical Operators Let's say you want to process a...
The VBA Range Object represents a cell or multiple cells in your Excel worksheet. It is the most important object of Excel VBA. By using Excel VBA range object, you can refer to,
As we discussed in our previous tutorial, that VBA is used to record and run Macro. But how VBA identify what data from the sheet needs to be executed. This is where VBA Range Objects is useful.
In this tutorial, you will learn-
Referencing Excel's VBA Range Object and the Object Qualifier.
To manipulate these cell values, Properties and Methods are used.
VBA follow object hierarchy pattern to refer object in Excel. You have to follow the following structure. Remember the .dot overhere connects the object at each of the different levels.
Application.Workbooks.Worksheets.Range
There are two main types of default objects.
Range property can be applied in two different types of objects.
Syntax for Range Property
Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1")
When you refer Range object, as shown above, it is referred as fully qualified reference. You have told Excel exactly which range you want, what sheet and in what worksheet.
Example: MsgBox Worksheet("sheet1").Range("A1").Value
Using Range property, you can perform many tasks like,
As such it will be too lengthy to cover all scenarios for range property. For scenarios mentioned above, we will demonstrate an example only for one. Refer to a Single cell using range property.
To refer to a single cell, you have to refer to a single cell.
Syntax is simple "Range("Cell")".
Here, we will use ".Select" command to select the single cell from the sheet.
Step 1) In this step, open your excel.
Step 2) In this step,
Step 3) In next step,
Step 4) The above step will open VBA code editor for file name "Single Cell Range". Enter the code as shown below for selecting range "A1" from the excel.
Step 5) Now save the file and run the program as shown below.
Step 6) You will see Cell "A1" is selected after execution of the program.
Likewise, you can select a cell with a particular Name. For example, if you want to search cell with name "Guru99- VBA Tutorial". You have to run the command as shown below. It will select the cell with that name.
Range("Guru99- VBA Tutorial").Select
To apply other range object here is the code sample.
Range for selecting cell in Excel | Range declared |
For single Row | Range("1:1") |
For single Column | Range("A: A") |
For Contiguous Cells | Range("A1:C5") |
For Non-Contiguous Cells | Range("A1:C5, F1:F5") |
For Intersection of two ranges | Range("A1:C5 F1:F5") (For intersection cell, remember there is no comma operator) |
To merge Cell | Range("A1:C5") ( To merge cell use "merge" command) |
Similarly to the range, in VBA you can also you "Cell Property". The only difference is that it has an "item" property that you use to reference the cells on your spreadsheet. Cell property is useful in a programming loop.
For example,
Cells.item(Row, Column). Both the lines below refer to cell A1.
Range offset property will select rows/columns away from its original position. On the basis of the range declared, cells are selected. See example below.
For example,
Range("A1").offset(Rowoffset:=1, Columnoffset:=1).Select
The result for this will cell B2. The offset property will move A1 cell to 1 column and 1 row away. You can change the value of rowoffset / columnoffset as per requirement. You can use a negative value (-1) to move cells backward.
Download Excel containing above code
Summary:
VBA Logical Operators: AND, OR, NOT Excel VBA Logical Operators Let's say you want to process a...
VBA Arithmetic Operators are used to perform arithmetic operations such as adding, subtracting,...
Variables are used in almost all computer program and VBA is no different. It's a good practice to...
What is Data Scraping? Data scraping is the technique that helps in the extraction of desired...
Everybody in this country should learn how to program a computer... because it teaches you how to...
What is an Excel Macro? Excel Macro is a record and playback tool that simply records your Excel...