Vb excel range

Excel VBA Range Object

What is VBA Range?

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,

• A single cell
• A row or a column of cells
• A selection of cells
• A 3-D range

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.

Introduction to Referencing Objects in VBA

Referencing Excel’s VBA Range Object and the Object Qualifier.

• Object Qualifier: This is used for referencing the object. It specifies the workbook or worksheet you are referring to.

To manipulate these cell values, Properties and Methods are used.

• Property: A property stores information about the object.
• Method: A method is an action of the object it will perform. Range object can perform actions like selected, copied, cleared, sorted, etc.

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.

How to refer to Excel VBA Range Object using Range property

Range property can be applied in two different types of objects.

• Worksheet Objects
• Range Objects

Syntax for Range Property

1. The keyword «Range.»
2. Parentheses that follow the keyword
3. Relevant Cell Range
4. Quotation (» «)

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,

• Refer to a Single cell using range property
• Refer to a Single cell using the Worksheet.Range Property
• Refer to an entire row or column
• Refer to merged cells using Worksheet.Range Property and many more

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.

Refer to a Single cell using the Worksheet.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,

• Click on button.
• It will open a window.
• Enter your program name here and click ‘OK’ button.
• It will take you to main Excel file, from top menu click on ‘stop’ record button to stop recording Macro.

Step 3) In next step,

• Click on Macro button from the top menu. It will open the window below.
• In this window, Click on the ‘edit’ button.

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.

Cell Property

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.

Cells.item(Row, Column). Both the lines below refer to cell A1.

• Cells.item(1,1) OR
• Cells.item(1,»A»)

Range Offset property

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.

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:

• The VBA Range Object represents a cell or multiple cells in your Excel worksheet
  • A single cell
  • A row or a column of cells
  • A selection of cells
  • A 3-D range
• To manipulate cell values, Properties and Methods are used
  • A property stores information about the object
  • A method is an action of the object it will perform like select, merge, sorted, etc.
• VBA follow object hierarchy pattern to refer object in Excel using .dot operator
• Range property can be applied in two different types of objects
  • Worksheet Objects
  • Range Objects

Range Object

The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA. This chapter gives an overview of the properties and methods of the Range object. Properties are something which an object has (they describe the object), while methods do something (they perform an action with an object).

Range Examples

Place a command button on your worksheet and add the following code line:

Result when you click the command button on the sheet:

Note: to refer to a named range in your Excel VBA code, use a code line like this:

Cells

Instead of Range, you can also use Cells. Using Cells is particularly useful when you want to loop through ranges.

Explanation: Excel VBA enters the value 2 into the cell at the intersection of row 3 and column 2.

Declare a Range Object

You can declare a Range object by using the keywords Dim and Set.

Dim example As Range
Set example = Range(«A1:C4»)

Select

An important method of the Range object is the Select method. The Select method simply selects a range.

Dim example As Range
Set example = Range(«A1:C4»)

Note: to select cells on a different worksheet, you have to activate this sheet first. For example, the following code lines select cell B7 on the third worksheet from the left.

The Rows property gives access to a specific row of a range.

Dim example As Range
Set example = Range(«A1:C4»)

Note: border for illustration only.

Columns

The Columns property gives access to a specific column of a range.

Dim example As Range
Set example = Range(«A1:C4»)

Note: border for illustration only.

Copy/Paste

The Copy and Paste method are used to copy a range and to paste it somewhere else on the worksheet.

Although this is allowed in Excel VBA, it is much better to use the code line below which does exactly the same.

Clear

To clear the content of an Excel range, you can use the ClearContents method.

Note: use the Clear method to clear the content and format of a range. Use the ClearFormats method to clear the format only.

Count

With the Count property, you can count the number of cells, rows and columns of a range.

Note: border for illustration only.

Dim example As Range
Set example = Range(«A1:C4»)

Dim example As Range
Set example = Range(«A1:C4»)

Note: in a similar way, you can count the number of columns of a range.

Analyst Cave

Excel VBA Range Tutorial

The VBA Range Object

The Excel Range Object is an object in Excel VBA that represents a cell, row, column, a selection of cells or a 3 dimensional range. The Excel Range is also a Worksheet property that returns a subset of its cells.

Worksheet Range

The Range is a Worksheet property which allows you to select any subset of cells, rows, columns etc.

Select a cell or Range of cells using the Select method. It will be visibly marked in Excel:
Select a single cell using the Range object

Working with Range variables

The Range is a separate object variable and can be declared as other variables. As the VBA Range is an object you need to use the Set statement:

The Range object defaults to your ActiveWorksheet. So beware as depending on your ActiveWorksheet the Range object will return values local to your worksheet:

You might want to define the Worksheet reference by Range if you want your reference values from a specifc Worksheet:

Range properties

The Range object contains a variety of properties with the main one being it’s Value and an the second one being its Formula.

A Range Value is the evaluated property of a cell or a range of cells. For example a cell with the formula =10+20 has an evaluated value of 20 .
A Range Formula is the formula provided in the cell or range of cells. For example a cell with a formula of =10+20 will have the same Formula property.

Other Range properties include:
Work in progress

Worksheet Cells

A Worksheet Cells property is similar to the Range property but allows you to obtain only a SINGLE CELL, based on its row and column index. Numbering starts at 1:
Select a single Cell using the Cells property

The Cells property is in fact a Range object not a separate data type.
Excel facilitates a Cells function that allows you to obtain a cell from within the ActiveSheet, current top-most worksheet.

Cells are Ranges which means they are not a separate data type:

Range Rows and Columns

As we all know an Excel Worksheet is divided into Rows and Columns. The Excel VBA Range object allows you to select single or multiple rows as well as single or multiple columns. There are a couple of ways to obtain Worksheet rows in VBA:

Getting an entire row or column

Range EntireRow property To get and entire row of a specified Range you need to use the EntireRow property. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the row number. Row indexing starts at 1.
Range EntireColumn property To get and entire column of a specified Range you need to use the EntireColumn property. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the column number. Column indexing starts at 1.

Get a row/column of a specified range

Range Rows function If you want to get a certain row within a Range simply use the Rows property of the Worksheet. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the row number. Row indexing starts at 1.
Range Columns property Similarly you can use the Columns function to obtain any single column within a Range. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex actually the first argument you provide will be the column index. Column indexing starts at 1.

To get a range of rows/columns you need to use the Range function like so:

Get row/column of specified range

The above approach assumed you want to obtain only rows/columns from the ActiveSheet – the visible and top-most Worksheet. Usually however, you will want to obtain rows or columns of an existing Range. Similarly as with the Worksheet Range property, any Range facilitates the Rows and Columns property.

Getting a Ranges first row/column number

As >Row and Column property which provide you with the number of the Ranges first row and column.

Converting Column number to Excel Column

This is an often question that turns up – how to convert a column number to a string e.g. 100 to “CV”.

Range Cut/Copy/Paste

Cutting and pasting rows is generally a bad practice which I heavily discourage as this is a practice that is moments can be heavily cpu-intensive and often is unaccounted for.

Copy function

Range copy function The Copy function works on a single cell, subset of cell or subset of rows/columns.

The Copy function can also be executed without an argument. It then copies the Range to the Windows Clipboard for later Pasting.

Cut function

Range Cut function The Cut function, similarly as the Copy function, cuts single cells, ranges of cells or rows/columns.

The Cut function can be executed without arguments. It will then cut the contents of the Range and copy it to the Windows Clipboard for pasting.

PasteSpecial function

Range PasteSpecial function The Range PasteSpecial function works only when preceded with either the Copy or Cut Range functions. It pastes the Range (or other data) within the Clipboard to the Range on which it was executed.

Syntax

The PasteSpecial function has the following syntax:

Parameters

Paste
The part of the Range which is to be pasted. This parameter can have the following values:

Operation
The paste operation e.g. paste all, only formatting, only values, etc. This can have one of the following values:

SkipBlanks
If True then blanks will not be pasted.

Transpose
Transpose the Range before paste (swap rows with columns).

Working with Excel tables & ranges: VB.NET code samples

I like grids. They can be an Excel spreadsheet, a table, or graph paper. I think I like them because they make me feel organized. This is probably because utilizing them automatically enforces structure to my thinking. Believe it or not, sometimes I need the help.

Microsoft Excel, being a spreadsheet application, automatically brings structure to your thinking. You can build models to help you think through just about anything. You can organize your data and create lists of any imaginable type. In fact, most apps in the mobile app stores these days can easily be replaced by Excel files. Even games. Don’t believe? Excel is gamers delight.

But I digress. Today is all about Excel tables and ranges and how to do some basic automation of them.

Working with tables

When creating Excel files, when are you not working with tables? I’m not sure but the answer if probably “often”… we’ll get to this situation later when I discuss ranges. An Excel table is a contiguous range of cells. Excel makes life easier for a user by automatically including rows and columns in the table (if no space exists between the table and what is added). Life is easier for the developer because you call a table by name (as well its columns). Of course, there are more features but I don’t want to regurgitate what’s available here.

Let’s look at some code samples to learn some of the basics.

Create a table

You create a table by calling the Add method of the ListObjects collection. This collection resides under a Worksheet object. Therefore, you need to first reference the worksheet that will be the table’s home. You then add it.

The CreateTable method accepts a Range and a String parameter. The range is the location for the table to be created. The string is the table’s name.

Insert a column or a row

If you have a table you want to automate, most likely you will want to add rows and columns to it. The InsertColumnOrRow method does both.

The sheet parameter is the worksheet that contains the table. The tableName parameter speaks for itself. The method calls the table by name to gain a reference to it. It then uses the rowOrColumn parameter to determine whether to add a column or row. The beforeRC parameter specifies the row or column to insert before. If this parameter is missing, we insert at the end of the table (at the right for columns, at the bottom for rows).

Sort a table

To sort a table, you need to build a query string and then add this string to the SortFields collection. When you believe the sort is properly set (and the compiler agrees), can apply by calling the Apply method of the Sort object.

The sort objects are children of the ListObjects collection. There is lot of object traversing, so be careful and make sure you attempting to build the sort with the correct objects. A big hint is that if the sort objects do not display in Intellisense, you are doing it wrong. I speak from experience.

Filter a table

Excel provides users with incredibly useful controls that allow the user to quickly filter a table at whim. Developers don’t care about fancy controls. We like a good object model combined with a challenging business rule or two. This can really get the blood flowing.

If you encounter a need to automate table filtering… no sweat, use AutoFilter.

To use AutoFilter, you need the table. This method accepts a table name as a parameter and then finds in the manner we recently covered. AutoFilter needs to know the field and the filter criteria. The field is an integer representing the column number to use for filter. The criteria is a string used as the filter.

Working with ranges

Ranges are like tables but with less structure. To user, they often resemble a table but we developers are smarter and wiser. We know they are different because they are different objects within the object model. Tables are ListObject objects (that sounds funny) and ranges are Range objects.

In the VB.NET code samples that follow, I have named ranges in mind.

Select a range

I like named ranges because I can call them directly… by their name. The SelectRange method uses the passed string to find the desired range in the Range collection.

This method works if you pass the name of a named range (e.g. “My super awesome named range”). It works if you specify the range using R1C1 notation (e.g. “B1:E29″).

Insert a column or a row

Just like with tables, adding columns and/or rows is a popular range-related automation activity.

This method finds the desired range and then adds a row or column as directed by the value of the rowOrColumn parameter.

Sort a range

To sort a range, you need to specify at least one key as well as the range to sort. In the SortRange method, rangeName is the Key1 and needs to be a string that serves as the filter criteria.

In this sample the Sort method utilizes the string value in sortByRange as Key1. The string can something like “A1″… just tell it the range used to key the sort.

Filter a range

Filtering a range is similar to sorting one. You need a range to filter but then you also need the column to filter on as well as the value to use as the filter.

The FitlerRange method uses the range object’s AutoFilter method to perform the filter. The filterField is an integer representing the range column to filter. The filterCriteria string contains the values to use as the filter.

Create a range (using an array)

I saved this one for last. While working with dynamic, say from a database, you might be tempted to build an array and then insert the array into an Excel range. This is perfectly reasonable and is easy to do if you know the potential bugaboo.

If you want to know all the details of this bugaboo, you can read about it later. The gist of it is this: you need to create at least two-dimensional array. This is true even if you only want to insert data into a single column. This is what CreateRangeWithArray does.

The sample method creates a 5 row, 2 column array. It then inserts 5 rows of data into it and inserts the array into a range. It sounds simple and maybe even obvious. But I’ve just saved a few hours of wondering “why doesn’t this work. ” caused by building a 1-dimension array. Dmitry saved you time too because this sample was his idea.

Okay, that’s all the time there is for today. These samples are a bit of Excel Tables and Ranges 101. Be careful out there when building solutions because clients will jump from the 100 level to the 401 level in the first 15 minutes of requirements gather. They can’t help it. Users mean well but they are shifty.

Available downloads:

This sample Excel add-in was developed using Add-in Express for Office and .net: