Excel range vba

Excel range vba DEFAULT

Home ➜ VBA Tutorial ➜

Key Notes

  • You can use the Range property as well as Cells property to use the Select property to select a range.

Select a Single Cell

To select a single cell, you need to define the cell address using the range, and then you need to use the select property. Let’s say if you want to select the cell A1, the code would be:

And if you want to use the CELLS, in that case, the code would be:

Select a Range of Cells

To select an entire range, you need to define the address of the range and then use the select property. For example, if you want to select the range A1 to A10, the code would be:

Select Non-Continues Range

To select a non-continuous range, you need to use a comma within the cell or range addresses, and then use the select the property. Let’s say if you want to select the range A1 to A10 and C5 to C10, the code would be:

And if you want to select single cells that are non-continuous, the code would be:

Select a Column

To select a column, let’s say column A, you need to write code like the following:

And if you want to select multiple columns, in that case, code would be like the following:

Select a Row

In the same way, if you want to select a row, let’s say row five, the code would be like following.

And for multiple rows, the code would be:

Select All the Cells of a Worksheet

Let’s say you want to select all the cells in the worksheet, just like you use the keyboard shortcut Control +A. You need to use the following code.

“Cells” refer to all the cells in the worksheet, and the select property selects them.

Select Cells with Data Only

Here “Cells with Data” only mean a section in the worksheet where cells have data and you can use the following code.

Select a Named Range

If you have a named range, you can select it by using its name.

In the above code, you have the “my_range” named range and then the select property, and when you run this macro, it selects the specified range.

Select an Excel Table

If you work with Excel tables, you can also select them suing the select property. Let’s say you have a table with the name “Data”, then the code to select that table would be:

If you want to select a column instead of the entire table, then the code would be, like the following:

And if you want to select the entire column including the header, then the code you can use:

Using OFFSET to Select a Range

You can also use the OFFSET property to select a cell or a range by navigating from a cell or a range. Let’s suppose you want to select a cell that is four columns right and five rows down from the A1; you can use the following code.

More on VBA Range and Cells

  • How to Set (Get and Change) Cell Value using a VBA Code
  • How to Sort a Range using VBA in Excel
  • How to Create a Named Range using VBA (Static + Dynamic) in Excel
  • How to Merge and Unmerge Cells in Excel using a VBA Code
  • How to Check IF a Cell is Empty using VBA in Excel
  • VBA ClearContents (from a Cell, Range, or Entire Worksheet)
  • Excel VBA Font (Color, Size, Type, and Bold)
  • How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
  • How to use OFFSET Property with the Range Object or a Cell in VBA
  • VBA Wrap Text (Cell, Range, and Entire Worksheet)
  • How to Copy a Cell\Range to Another Sheet using VBA
  • How to use Range/Cell as a Variable in VBA in Excel
  • How to Find Last Rows, Column, and Cell using VBA in Excel
  • How to use ActiveCell in VBA in Excel
  • How to use Special Cell Method in VBA in Excel
  • How to Apply Borders on a Cell using VBA in Excel
  • How to Refer to the UsedRange using VBA in Excel
  • How to Change Row Height/Column Width using VBA in Excel
  • How to Select All the Cells in a Worksheet using a VBA Code
  • How to Insert a Row using VBA in Excel
  • How to Insert a Column using VBA in Excel
Sours: https://excelchamps.com/vba/select-range/

Home ➜ VBA Tutorial ➜

Introduction to Range and Cells in VBA

When you look around in an Excel workbook, you will find that everything works around cells. A cell and a range of cells are where you store your data, and then everything starts.

To make the best of VBA, you need to learn how to use cells and ranges in your codes. For this, you need to have a solid understanding of Range Object. By using it, you can refer to cells in your codes in the following ways:

  • A single cell.
  • A range of cells
  • A row or a column
  • A three-dimensional range

The RANGE OBJECT is a part of Excel’s Object Hierarchy: Application ➜ Workbooks ➜ Worksheets ➜ Range and besides inside the worksheet. So if you are writing code to refer to the RANGE object it would be like this:

By referring to a cell or range of cells, you can do the following things:

  • You can read the value from it.
  • You can enter a value in it.
  • And, you can make changes to the format.

To do all these things, you need to learn to refer to a cell or a range of cells, and in the next section of this tutorial, you will learn to refer to a cell using different ways.

To refer to a cell or a range of cells, you can use three different ways.

  • Range Property
  • Cells Property
  • Offset Property

Well, which one is best out of these depends on your requirement, but it is worth learning all three so that you can choose which one is perfect for you.

So let’s get started.

Range Property

Range property is the most common and popular way to refer to a range in your VBA codes. With Range property, you simply need to refer to the cell address. Let me tell you the syntax.

Here the expression is a variable representing a VBA object. So if you need to refer to the cell A1, the line of code you need to write would be:

The above code tells VBA that you are referring to the cell A1 which is in the worksheet “Sheet1” and workbook ”Book1”.

Note: Whenever you type a cell address in the range object, make sure to wrap it in the double quotation marks. But here’s one thing to understand. As you are using VBA in Excel there’s no need to use the word “Application”. So the code would be:

And if you are in the Book1 there you can further trim down your code:

But, if you are already in the worksheet “Sheet1” then you can further trim down your code and can only use:

Now, let’s say if you want to refer to a full range of cells (i.e., multiple cells) you need to write the code in the following way:

In the above code, you have referred to the range A1 to A5 which consists of the five cells. You can also refer to a named range using the range object. Let’s say you have named range with the name of “Sales Discount” to refer to this you can write a code like this:

If you want to refer to a non-continues range then you need to do something like this:

And if you want to refer to an entire row or a column then you need to enter code like below:

At this point, you have a clear understanding of how to refer to a cell and range of cells. But to make it best with this you need to learn how to use this to do other things.

here we have a complete list of tutorials that you can use to learn to work with ranges and cells in VBA

  • How to SET (Get and Change) Cell Value using a VBA Code
  • How to Select a Range using VBA in Excel
  • How to Create a Named Range using VBA (Static + Dynamic) in Excel
  • How to Merge and Unmerge Cells in Excel using a VBA Code
  • How to Check IF a Cell is Empty using VBA in Excel
  • VBA ClearContents (from a Cell, Range, or Entire Worksheet)
  • Excel VBA Font (Color, Size, Type, and Bold)
  • How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
  • How to use OFFSET Property with the Range Object or a Cell in VBA
  • VBA Wrap Text (Cell, Range, and Entire Worksheet)
  • How to Copy a Cell\Range to Another Sheet using VBA
  • How to use Range/Cell as a Variable in VBA in Excel
  • How to Find Last Rows, Column, and Cell using VBA in Excel
  • How to use ActiveCell in VBA in Excel
  • How to Refer to the UsedRange using VBA in Excel
  • How to Change Row Height/Column Width using VBA in Excel
  • How to SELECT ALL the Cells in a Worksheet using a VBA Code
  • How to Insert a Row using VBA in Excel
  • How to Insert a Column using VBA in Excel

1. Select and Activate a Cell

If you want to select a cell then you can use the Range.Select method. Let’s say if you want to select the cell A5 then all you need to do is specify the range and then add “.Select” after that.

This code tells VBA to select the cell A5 and if you want to select a range of cells then you just need to refer to that range and simply add “.Select” after that.

There’s also another method which you can use to activate a cell.

Here you need to remember that you can activate only one cell at a time. Even if you specify a range with the “.Activate method, it will select that range but the active cell will be the first cell of the range.

2. Enter a Value in a Cell

By using the range property you can enter a value in a cell or a range of cells. Let’s understand how it works using a simple example:

In the above example, you have specified the A1 as a range and after that, you have added “.Value” which tells VBA to access the value property of the cell.

The next thing you have is the equals sign and then the value which you want to enter (you need to use double quotation marks if you are entering a text value). For a number, the code would like this:

And if you want to enter a value into a range of cells, I mean multiple cells, then all you need to do is specify that range. 

And, here’s the code if you are referring to the non-continues range.

3. Copy and Paste a Cell/Range

With Range property, you can use the “.Copy method to copy and cell and then paste it into a destination cell. Let’s say if you need to copy the cell A5 the code for this would be:

When you run this code it will simply copy the cell A5 but the next thing is to paste this copied cell to a destination cell. For this, you need to add the keyword destination after it and followed by the cell where you want to paste it. So if you want to copy the cell A1 and then want to paste it to the cell E5, the code would be:

In the same way, if you are dealing with a range of multiple cells then the code would be like:

If you have copied a range of cells and then if you have mentioned one cell as the destination range, VBA will copy the entire copied range into the starting from the cell you have specified as a destination.

When you run the above code, VBA will copy range A1:A5 and will paste it to the B1:B5 even though you have mentioned only B1 as destination range.

Tip: Just like the “.Copy” method you can use “.Cut” method to cut a cell and then simply use a destination to paste it.

4. Use Font Property with Range Property

With range property, you can access the font property of a cell which helps you to change all the font settings. There are a total of 18 different properties for the font which you can access. Let’s say if you want to make the text BOLD in cell A1, the code would be:

This code tells VBA to access the BOLD property of the font which is inside the range A1 and you have set this property to TRUE. Now, let’s say you want to apply strikethrough to the cell A1, this time the code would be:

As I said there are a total of 18 different properties you can use, so make sure to check out all of these to see which one is useful for you.

5. Clear Formatting from a Cell

By using the “.ClearFormats” method you can remove only the format from a cell or a range of cells. All you need to do is add “.ClearFormat” after specifying the range, like below:

When you run the code above it clears all the formatting from cell A1 and if you want to do it for an entire range, you know what to do, Right?

Now the above code will simply remove the formatting from the range A1 to A5.

Cells Property

Apart from the RANGE property, you can use the “Cells” property to refer to a cell or a range of cells in your worksheet. In cell property, instead of using the cell reference, you need to enter the column number and row number of the cell.

Here the expression is a VBA object and Row_Number is the row number of the cell and Column_Number is the column of the cell. So if you want to refer to the cell A5 you can use the code the below code:

Now, this code tells VBA to refer to the cell which is at row number five and at the column number one. As it’s syntax says you need to enter column number as address but the reality is you can also use the column alphabet if you want just by wrapping it in double quotation marks.

Below code will also refer to the cell A5:

And to VBA to select it simply add “.Select” at the end.

The above code will select the cell A5 which is in the 5th row and in the first column of the worksheet.

OFFSET Property

If you want to play well with ranges in VBA you need to know how to use OFFSET property. It helps to refer to a cell that is a particular number of rows and columns away from another cell.

Let’s say your active cell is B5 right now and you want to navigate to the cell which is 3 columns right and 1 row down from B5, you can do this OFFSET. Below is the syntax which you need to use for the OFFSET:

  • RowOffset: In this argument, you need to specify a number that will tell VBA how many rows you want to navigate. A positive number defines a row downward and a negative number defines a row upward.
  • ColumnOffset: In this argument, you need to specify a number that will tell VBA how many columns you want to navigate. A positive number defines a column to the right and a negative number defines a left. 

Let’s write a simple code for example which we have discussed above.

So when you run this code it will select the cell which is one row down and 3 columns right from the cell B5.

Resize a Range using OFFSET

OFFSET not only allows you to navigate to a cell, but you can also resize the range further. Let’s continue the above example.

The above code navigates you to the cell E6, and now let’s say you need to select the range of cells that consists of the five columns and three rows from the E6. So what you need to do is after using OFFSET, use the resize property by adding “.Resize”.

Now you need to enter the row size and column size. Type a starting parenthesis and enter the number to define the row size and then a number to define the column size.

In the end, add “.Select” to tell VBA to select the range, and when you run this code, it will select the range.

So when you run this code, it will select the range E6 to I8.

Sours: https://excelchamps.com/vba/range/
  1. Daytona qualifying races
  2. Lgk10 t mobile
  3. Craigslist jackson ms

Range object (Excel)

Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3D range.

Note

Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.

The default member of Range forwards calls without parameters to the Value property and calls with parameters to the Item member. Accordingly, is equivalent to , to and to .

The following properties and methods for returning a Range object are described in the Example section:

  • Range and Cells properties of the Worksheet object
  • Range and Cells properties of the Range object
  • Rows and Columns properties of the Worksheet object
  • Rows and Columns properties of the Range object
  • Offset property of the Range object
  • Union method of the Application object

Example

Use Range (arg), where arg names the range, to return a Range object that represents a single cell or a range of cells. The following example places the value of cell A1 in cell A5.


The following example fills the range A1:H8 with random numbers by setting the formula for each cell in the range. When it's used without an object qualifier (an object to the left of the period), the Range property returns a range on the active sheet. If the active sheet isn't a worksheet, the method fails.

Use the Activate method of the Worksheet object to activate a worksheet before you use the Range property without an explicit object qualifier.


The following example clears the contents of the range named Criteria.

Note

If you use a text argument for the range address, you must specify the address in A1-style notation (you cannot use R1C1-style notation).


Use Cells on a worksheet to obtain a range consisting all single cells on the worksheet. You can access single cells via Item(row, column), where row is the row index and column is the column index. Item can be omitted since the call is forwarded to it by the default member of Range. The following example sets the value of cell A1 to 24 and of cell B1 to 42 on the first sheet of the active workbook.


The following example sets the formula for cell A2.


Although you can also use to return cell A1, there may be times when the Cells property is more convenient because you can use a variable for the row or column. The following example creates column and row headings on Sheet1. Be aware that after the worksheet has been activated, the Cells property can be used without an explicit sheet declaration (it returns a cell on the active sheet).

Note

Although you could use Visual Basic string functions to alter A1-style references, it is easier (and better programming practice) to use the notation.


Use_expression_.Cells, where expression is an expression that returns a Range object, to obtain a range with the same address consisting of single cells. On such a range, you access single cells via Item(row, column), where are relative to the upper-left corner of the first area of the range. Item can be omitted since the call is forwarded to it by the default member of Range. The following example sets the formula for cell C5 and D5 of the first sheet of the active workbook.


Use Range (cell1, cell2), where cell1 and cell2 are Range objects that specify the start and end cells, to return a Range object. The following example sets the border line style for cells A1:J10.

Note

Be aware that the period in front of each occurrence of the Cells property is required if the result of the preceding With statement is to be applied to the Cells property. In this case, it indicates that the cells are on worksheet one (without the period, the Cells property would return cells on the active sheet).


Use Rows on a worksheet to obtain a range consisting all rows on the worksheet. You can access single rows via Item(row), where row is the row index. Item can be omitted since the call is forwarded to it by the default member of Range.

Note

It is not legal to provide the second parameter of Item for ranges consisting of rows. You first have to convert it to single cells via Cells.

The following example deletes row 4 and 10 of the first sheet of the active workbook.


Use Columns on a worksheet to obtain a range consisting all columns on the worksheet. You can access single columns via Item(row) [sic], where row is the column index given as a number or as an A1-style column address. Item can be omitted since the call is forwarded to it by the default member of Range.

Note

It is not legal to provide the second parameter of Item for ranges consisting of columns. You first have to convert it to single cells via Cells.

The following example deletes column "B", "C", "E", and "J" of the first sheet of the active workbook.


Use_expression_.Rows, where expression is an expression that returns a Range object, to obtain a range consisting of the rows in the first area of the range. You can access single rows via Item(row), where row is the relative row index from the top of the first area of the range. Item can be omitted since the call is forwarded to it by the default member of Range.

Note

It is not legal to provide the second parameter of Item for ranges consisting of rows. You first have to convert it to single cells via Cells.

The following example deletes the ranges C8:D8 and C6:D6 of the first sheet of the active workbook.


Use_expression_.Columns, where expression is an expression that returns a Range object, to obtain a range consisting of the columns in the first area of the range. You can access single columns via Item(row) [sic], where row is the relative column index from the left of the first area of the range given as a number or as an A1-style column address. Item can be omitted since the call is forwarded to it by the default member of Range.

Note

It is not legal to provide the second parameter of Item for ranges consisting of columns. You first have to convert it to single cells via Cells.

The following example deletes the ranges L2:L10, G2:G10, F2:F10 and D2:D10 of the first sheet of the active workbook.


Use Offset (row, column), where row and column are the row and column offsets, to return a range at a specified offset to another range. The following example selects the cell three rows down from and one column to the right of the cell in the upper-left corner of the current selection. You cannot select a cell that is not on the active sheet, so you must first activate the worksheet.


Use Union (range1, range2, ...) to return multiple-area ranges—that is, ranges composed of two or more contiguous blocks of cells. The following example creates an object defined as the union of ranges A1:B2 and C3:D4, and then selects the defined range.


If you work with selections that contain more than one area, the Areas property is useful. It divides a multiple-area selection into individual Range objects and then returns the objects as a collection. You can use the Count property on the returned collection to verify a selection that contains more than one area, as shown in the following example.


This example uses the AdvancedFilter method of the Range object to create a list of the unique values, and the number of times those unique values occur, in the range of column A.

Methods

Properties

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Sours: https://docs.microsoft.com/en-us/office/vba/api/excel.range(object)

How to select cells/ranges by using Visual Basic procedures in Excel

  • 8 minutes to read
  • Applies to:
    Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The examples in this article use the Visual Basic methods listed in the following table.

The examples in this article use the properties in the following table.

How to Select a Cell on the Active Worksheet

To select cell D5 on the active worksheet, you can use either of the following examples:

How to Select a Cell on Another Worksheet in the Same Workbook

To select cell E6 on another worksheet in the same workbook, you can use either of the following examples:

Or, you can activate the worksheet, and then use method 1 above to select the cell:

How to Select a Cell on a Worksheet in a Different Workbook

To select cell F7 on a worksheet in a different workbook, you can use either of the following examples:

Or, you can activate the worksheet, and then use method 1 above to select the cell:

How to Select a Range of Cells on the Active Worksheet

To select the range C2:D10 on the active worksheet, you can use any of the following examples:

How to Select a Range of Cells on Another Worksheet in the Same Workbook

To select the range D3:E11 on another worksheet in the same workbook, you can use either of the following examples:

Or, you can activate the worksheet, and then use method 4 above to select the range:

How to Select a Range of Cells on a Worksheet in a Different Workbook

To select the range E4:F12 on a worksheet in a different workbook, you can use either of the following examples:

Or, you can activate the worksheet, and then use method 4 above to select the range:

How to Select a Named Range on the Active Worksheet

To select the named range "Test" on the active worksheet, you can use either of the following examples:

How to Select a Named Range on Another Worksheet in the Same Workbook

To select the named range "Test" on another worksheet in the same workbook, you can use the following example:

Or, you can activate the worksheet, and then use method 7 above to select the named range:

How to Select a Named Range on a Worksheet in a Different Workbook

To select the named range "Test" on a worksheet in a different workbook, you can use the following example:

Or, you can activate the worksheet, and then use method 7 above to select the named range:

How to Select a Cell Relative to the Active Cell

To select a cell that is five rows below and four columns to the left of the active cell, you can use the following example:

To select a cell that is two rows above and three columns to the right of the active cell, you can use the following example:

Note

An error will occur if you try to select a cell that is "off the worksheet." The first example shown above will return an error if the active cell is in columns A through D, since moving four columns to the left would take the active cell to an invalid cell address.

How to Select a Cell Relative to Another (Not the Active) Cell

To select a cell that is five rows below and four columns to the right of cell C7, you can use either of the following examples:

How to Select a Range of Cells Offset from a Specified Range

To select a range of cells that is the same size as the named range "Test" but that is shifted four rows down and three columns to the right, you can use the following example:

If the named range is on another (not the active) worksheet, activate that worksheet first, and then select the range using the following example:

How to Select a Specified Range and Resize the Selection

To select the named range "Database" and then extend the selection by five rows, you can use the following example:

How to Select a Specified Range, Offset It, and Then Resize It

To select a range four rows below and three columns to the right of the named range "Database" and include two rows and one column more than the named range, you can use the following example:

How to Select the Union of Two or More Specified Ranges

To select the union (that is, the combined area) of the two named ranges "Test" and "Sample," you can use the following example:

Note

that both ranges must be on the same worksheet for this example to work. Note also that the Union method does not work across sheets. For example, this line works fine.

but this line

returns the error message:

Union method of application class failed

How to Select the Intersection of Two or More Specified Ranges

To select the intersection of the two named ranges "Test" and "Sample," you can use the following example:

Note that both ranges must be on the same worksheet for this example to work.

Examples 17-21 in this article refer to the following sample set of data. Each example states the range of cells in the sample data that would be selected.

How to Select the Last Cell of a Column of Contiguous Data

To select the last cell in a contiguous column, use the following example:

When this code is used with the sample table, cell A4 will be selected.

How to Select the Blank Cell at Bottom of a Column of Contiguous Data

To select the cell below a range of contiguous cells, use the following example:

When this code is used with the sample table, cell A5 will be selected.

How to Select an Entire Range of Contiguous Cells in a Column

To select a range of contiguous cells in a column, use one of the following examples:

When this code is used with the sample table, cells A1 through A4 will be selected.

How to Select an Entire Range of Non-Contiguous Cells in a Column

To select a range of cells that are non-contiguous, use one of the following examples:

When this code is used with the sample table, it will select cells A1 through A6.

How to Select a Rectangular Range of Cells

In order to select a rectangular range of cells around a cell, use the CurrentRegion method. The range selected by the CurrentRegion method is an area bounded by any combination of blank rows and blank columns. The following is an example of how to use the CurrentRegion method:

This code will select cells A1 through C4. Other examples to select the same range of cells are listed below:

In some instances, you may want to select cells A1 through C6. In this example, the CurrentRegion method will not work because of the blank line on Row 5. The following examples will select all of the cells:

How to Select Multiple Non-Contiguous Columns of Varying Length

To select multiple non-contiguous columns of varying length, use the following sample table and macro example:

When this code is used with the sample table, cells A1:A3 and C1:C6 will be selected.

Notes on the examples

The ActiveSheet property can usually be omitted, because it is implied if a specific sheet is not named. For example, instead of

you can use:

The ActiveWorkbook property can also usually be omitted. Unless a specific workbook is named, the active workbook is implied.

When you use the Application.Goto method, if you want to use two Cells methods within the Range method when the specified range is on another (not the active) worksheet, you must include the Sheets object each time. For example:

For any item in quotation marks (for example, the named range "Test"), you can also use a variable whose value is a text string. For example, instead of

you can use

where the value of myVar is "Sheet1".

Sours: https://docs.microsoft.com/en-us/previous-versions/office/troubleshoot/office-developer/select-cells-rangs-with-visual-basic

Vba excel range

Excel VBA Range Object

Range is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.

For example, the range property in VBA is used to refer to specific rows or columns while writing the code. The code “Range(“A1:A5”).Value=2” returns the number 2 in the range A1:A5.

In VBA, macros are recorded and executed to automate the Excel tasks. This helps perform the repetitive processes in a faster and more accurate way. For running the macros, VBA identifies the cells on which the called tasks are to be performed. It is here that the range object in VBA comes in use.

The VBA range property is similar to the worksheet property and has several applications.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkArticle Link to be Hyperlinked
For eg:
Source: VBA Range (wallstreetmojo.com)

How to use the VBA Range Function in Excel?

A hierarchy pattern is used in Excel VBA to refer to the range object. This three-level object hierarchy consists of the following elements:

  • Object Qualifier: This refers to the location of the object. It is the workbook or the worksheet where the object is placed.
  • Property: This stores the information related to the object.
  • Method: This refers to the action that the object will perform. For example, for a given range, the methods are actions like sorting, formatting, selecting, clearing, etc.

The given hierarchical structure is to be followed whenever a VBA objectThe “Object Required” error, also known as Error 424, is a VBA run-time error that occurs when you provide an invalid object, i.e., one that is not available in VBA's object hierarchy.read more is referred. These three elements are separated by the dot operator (.) in the following way:

Application.Workbooks.Worksheets.Range

Note 1: The range object referred by using the given (three-level) hierarchy is known as a fully qualified reference.

Note 2: The “property” and “method” are used for manipulating cell values.

The Syntax of the VBA Range Property

The syntax of the range property in VBA is shown in the following image:

Range Syntax

For example, to refer to the cell B1 (range) in “sheet3” (worksheet) of “booknew” (workbook), the following reference is used:

Application.Workbooks(“Booknew.xlsm”).Worksheets(“Sheet3”).Range(“B1”)

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

Example #1–Select a Single Cell

We want to select the cell B2 in “sheet1” of the workbook.

Step 1: Open the workbook saved with the Excel extensionExcel extensions represent the file format. It helps the user to save different types of excel files in various formats. For instance, .xlsx is used for simple data, and XLSM is used to store the VBA code.read more “.xlsm” (macro-enabled workbook). The “.xlsx” format does not allow saving the macros that are presently being written.

Step 2: Open the VBA editorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more with the shortcut “ALT+F11.” Alternatively, click “view code” in the “controls” group of the Developer tab, as shown in the following image.

VBA Range

Step 3: The screen, shown in the following image, appears.

VBA Range

Step 4: Enter the following code.

Public  Sub SingleCellRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“B2”).Select
End Sub

With this code, we instruct the program to go to the specified cell (B2) of a particular worksheet and workbook. The action to be performed is to select the given cell.

At present, cell A2 is activated, as shown in the following image.

VBA Range 1

Step 5: Run the code by clicking “run sub/UserForm” from the Run tab. Alternatively, use the Excel shortcut key F5 to run the code.

VBA Range 2

Step 6: The result is shown in the following image. The cell B2 is selected after the execution of the program. Hence, after running the code, the activated cell is B2.

VBA Range 3

Similarly, the code can be modified to select a variety of cells and ranges and perform different actions on them.

Example #2–Select an Entire Row

We want to select the second row in “sheet1” of the workbook.

Step 1: Enter the following code and run it.

Public  Sub EntireRowRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“2:2”).Select
End Sub

The range (“2:2”) in the code represents the second row.

VBA Range 4

Step 2: The result is shown in the following image. The second row is entirely selected by running the given code.

VBA Range 5

Example #3–Select an Entire Column

We want to select the column C in “sheet1” of the workbook.

Step 1: Enter the following code and run it.

Public  Sub EntireColumnRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“C:C”).Select
End Sub

The range (“C:C”) in the code represents the column C.

Step 2: The execution of the code and the result is shown in the following image. The column C is entirely selected by running the given code. 

VBA Range 6

Similarly, one can select contiguous and non-contiguous cells, the intersection of cell ranges, etc.

Example #4–Select Contiguous Cells

We want to select the range B2:D6 in “sheet1” of the workbook.

Step 1: Enter the following code and run it.

Public  Sub EntireColumnRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“B2:D6”).Select
End Sub

The range (“B2:D6”) in the code represents the contiguous range B2:D6.

Step 2: The result is shown in the following image. The range B2:D6 is selected by running the given code.

VBA Range 8

Example #5–Select Non-contiguous Cells

We want to select the ranges B1:C5 and G1:G3 in “sheet1” of the workbook.

Step 1: Enter the following code and run it.

Public  Sub EntireColumnRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“B1:C5, G1:G3”).Select
End Sub

The range (“B1:C5, G1:G3”) in the code represents the two non-contiguous ranges B1:C5 and G1:G3.

Step 2: The result is shown in the following image. The ranges B1:C5 and G1:G3 are selected by running the given code.

visual basic appplication 9

Example #6–Select a Range Intersection

We want to select the intersection of two ranges B1:G5 and G1:G3 in “sheet1” of the workbook.

Step 1: Enter the following code and run it.

Public  Sub EntireColumnRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“B1:G5 G1:G3”).Select
End Sub

The range (“B1:G5 G1:G3”) in the code represents the intersection of the two ranges B1:G5 and G1:G3.

Note: The comma within the two ranges is absent in this case.

Step 2: The result is shown in the following image. The cells common to the ranges B1:G5 and G1:G3 are selected by running the given code. Hence, the common cells in the specified range are G1:G3.

visual basic appplication 10

Example #7–Merge a Range of Cells

We want to select and merge the cells B1:C5 in “sheet1” of the workbook.

Step 1: Enter the following code and run it.

Public  Sub MergeRange()
ThisWorkbook.Worksheets (“Sheet1”).Range(“B1:C5”).Merge
End Sub

The range (“B1:C5”) represents the range B1:C5 on which we are performing the action “merge.”

Step 2: The result is shown in the following image. The cells in the range B1:C5 have been merged by running the given code.

visual basic appplication 11

Example #8–Clear Formatting of a Range

The following image shows the range F2:H6, which is highlighted in yellow. We want to clear the Excel formattingFormatting is a useful feature in Excel that allows you to change the appearance of the data in a worksheet. Formatting can be done in a variety of ways. For example, we can use the styles and format tab on the home tab to change the font of a cell or a table.read more on this range in “sheet1” of the workbook.

visual basic appplication 12

Step 1: Enter the following code and run it.

Public  Sub ClearFormats()
ThisWorkbook.Worksheets(“Sheet1”).Range(“F2:H6”).ClearFormats
End Sub

The range (“F2:H6”) in the given syntaxVBA ThisWorkbook refers to the workbook on which the users currently write the code to execute all of the tasks in the current workbook. In this, it doesn't matter which workbook is active and only requires the reference to the workbook, where the users write the code.read more represents the range F2:H6 from which we are removing the existing formatting.

Step 2: The result is shown in the following image. The formatting from the cells in the range F2:H6 has been cleared by running the given code.

visual basic appplication 13

Similarly, the formatting of the entire worksheet can be removed. Moreover, the content of a range of cells can be cleared by using the action “.ClearContents.”

Frequently Asked Questions

1. Define the VBA range.

The VBA range represents a single cell, a row, a column, a group of cells, or a three-dimensional range. The range must be specified in the following hierarchical pattern:

Application.Workbooks.Worksheets.Range

The references following this format are known as fully qualified references. For simplifying these references, the “application” object can be omitted. In such cases, VBA assumes that the user is working on MS Excel.

For example, a simplified reference is Workbooks(“Book2.xlsm”).Worksheets(“Sheet2”).Range

The basic syntax of the VBA range property consists of the keyword “Range” followed by the parentheses. The relevant range is included within double quotation marks.

For example, the following reference refers to cell C1 in the given worksheet and workbook.

Application.Workbooks(“Book2.xlsm”).Worksheets(“Sheet2”).Range(“C1”)

2. How to copy and paste the VBA range?

The following syntax helps copy the range C1:C5:

Range(“C1:C5”).Copy

The following syntax helps copy the range C1:C5 from the present sheet to “sheet3”:
Range(“C1:C5”).Copy destination:= Worksheets(“Sheet3”).Range(“D1”)

The following syntax helps paste in cell D1 of “sheet3”:

Worksheets(“Sheet3”).Range(“D1”).PasteSpecial

Note: Alternatively, the range can be first selected and the resulting selection can be copied.

3. How to clear cells in a VBA range?

The following syntax helps clear the values, comments, and formats from the range C1:C5:

Range(“C1:C5”).Clear

The following syntax helps clear the content or values from the range C1:C5:

Range(“C1:C5”).ClearContents

Note: The clearing methods “ClearNotes,” “ClearComments,” “ClearOutline,” “ClearHyperlinks,” and so on clear particular types of data from the range.

Recommended Articles

This has been a guide to VBA Range. Here we learn how to select a particular cell and range of cells with the help of VBA range objects and examples in Excel. You may also have a look at other articles related to Excel VBA –

VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Sours: https://www.wallstreetmojo.com/vba-range/
Excel VBA Tips n Tricks 41 Referencing Named Range from Other Workbook Q n A

Range Object

Range Examples | Cells | Declare a Range Object | Select | Rows | Columns | Copy/Paste | Clear | Count

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:

Range("B3").Value = 2

Result when you click the command button on the sheet:

Excel VBA Range Example

Code:

Range("A1:A4").Value = 5

Result:

Range Example

Code:

Range("A1:A2,B3:C4").Value = 10

Result:

Range Example

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

Range("Prices").Value = 15

Cells

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

Code:

Cells(3, 2).Value = 2

Result:

Cells in Excel VBA

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

Code:

Range(Cells(1, 1), Cells(4, 1)).Value = 5

Result:

Cells

Declare a Range Object

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

Code:

Dim example As Range
Set example = Range("A1:C4")

example.Value = 8

Result:

Declare a Range Object in Excel VBA

Select

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

Code:

Dim example As Range
Set example = Range("A1:C4")

example.Select

Result:

Select Method

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.

Worksheets(3).Activate
Worksheets(3).Range("B7").Select

Rows

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

Code:

Dim example As Range
Set example = Range("A1:C4")

example.Rows(3).Select

Result:

Rows Property

Note: border for illustration only.

Columns

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

Code:

Dim example As Range
Set example = Range("A1:C4")

example.Columns(2).Select

Result:

Columns Property

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.

Code:

Range("A1:A2").Select
Selection.Copy

Range("C3").Select
ActiveSheet.Paste

Result:

Copy/Paste Method

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

Range("C3:C4").Value = Range("A1:A2").Value

Clear

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

Range("A1").ClearContents

or simply use:

Range("A1").Value = ""

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.

Count Property

Note: border for illustration only.

Code:

Dim example As Range
Set example = Range("A1:C4")

MsgBox example.Count

Result:

Count Cells

Code:

Dim example As Range
Set example = Range("A1:C4")

MsgBox example.Rows.Count

Result:

Count Rows

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

Sours: https://www.excel-easy.com/vba/range-object.html

You will also like:

VBA Code


No Cell Object

There is no Cell object nor is there a Cells collection.
Individual cells are treated as Range objects that refer to one cell.


Contents of a Cell

The easiest way to find what the contents of a cell are is to use the Visual Basic TypeName function

Range("A1").Value = 100
Range("B1").Value = VBA.TypeName(Range("A1").Value) = "Double"

Range("A2").Value = #1/1/2006#
Range("B2").Value = VBA.TypeName(Range("A2").Value) = "Date"

Range("A3").Value = "some text"
Range("B3").Value = VBA.TypeName(Range("A3").Value) = "String"

Range("A4").Formula = "=A1"
Range("B4").Value = VBA.TypeName(Range("A4").Value) = "Double"

Text Property

You can assign a value to a cell using its Value property.
You can give a cell a number format by using its NumberFormat property.
The Text property of a cell returns the formatted appearance of the contents of a cell.

Range("A1").Text = "$12,345.00"

Range Object

The Range object can consist of individual cells or groups of cells.
Even an entire row or column is considered to be a range.
Although Excel can work with three dimensional formulas the Range object in VBA is limited to a range of cells on a single worksheet.
It is possible to edit a range either using a Range object directly (e.g. Range("A1").BackColor ) or by using the ActiveCell or Selection methods (e.g. ActiveCell.BackColor )


Cells Property

Application.Cells
ActiveSheet.Cells
ActiveCell
Cells

When the cells property is applied to a Range object the same object is returned.
It does have some uses though:

  • Range.Cells.Count - The total number of cells in the range.

  • Range.Cells(row, column) - To refer to a specific cell within a range.

ActiveSheet.Cells(2,2) = ActiveSheet.Range("B2")
  • To loop through a range of cells

For irow = 1 to 4
   For icolumn = 1 to 4
      ActiveSheet.Cells(irow,icolumn).Value = 10
   Next icolumn
Next irow

Dim objRange As Range
Set objRange = ActiveSheet.Range(ActiveSheet.Cells(1,4), ActiveSheet.Cells(2,6))

Cells automatically refer to the active worksheet.
If you want to access cells on another worksheet then the correct code is:

Range( Worksheets(n).Cells(""), Worksheets(n).Cells("") )

Range Property

Application.Range
ActiveSheet.Range
Range

When Range is not prefixed and used in a worksheet module, then it refers to that specific worksheet and not the active worksheet.


Selection Property

Selection will return a range of cells
Be aware that the Selection will not refer to a Range object if another type of object, such as a chart or shape is currently selected.
Using the Selection object performs an operation on the currently selected cells.
If a range of cells has not been selected prior to this command, then the active cell is used.

Selection.Resize(4,4).Select

It is always worth checking what is currently selected before using the Selection property.

If TypeName(Selection) = "Range" Then
EndIf

Relative References

It is important to remember that when a Cells property or a Range property is applied to a Range object, all the references are relative to the upper-left corner of that range.

ActiveSheet.Range("B2:D4").Cells(2,2) = ActiveSheet.Range("C3")
ActiveSheet.Range("B2:D4").Range("B2") = ActiveSheet.Range("C3")

ActiveCell returns a reference to the currently active cell
This will only ever return a single cell, even when a range of cells is selected.
The active cell will always be one of the corner cells of a range of cells. - will it what if you use the keyboard ??


Total number of populated cells

iTotal = Application.CountA(ActiveSheet.Cells)

Window Object Only

This property applies only to a window object
This will enter the value 12 into the range that was selected before a non-range object was selected.

ActiveWindow.RangeSelection.Value = 12

Window.RangeSelection property is read-only and returns a Range object that represents the selected cells on the worksheet in the active window.
If a graphic object is active or selected then this will returns the range of cells that was selected before the graphic object was selected.


Counting

Dim ltotal AsLong
ltotal = Cells.Count 'returns a long

Dim dbtotal AsDouble
dbtotal = Cells.CountLarge ' returns a double

Range of a Range

It is possible to treat a Range as if it was the top left cell in the worksheet.
This can be used to return a reference to the upper left cell of a Range object
The following line of code would select cell "C3".

Range("B2").Range("B2").Select

Range("B2").Cells(2).Select

Remember that cells are numbered starting from A1 and continuing right to the end of the row before moving to the start of the next row.

Range("B2").Cells(2,2).Select

An alternative to this is to use the Offset which is more intuitive.


Range.Address

The Address method returns the address of a range in the form of a string.

Range("A1:B3").Address = "$A$1:$B$3"

Using the parameters allows you to control the transformation into a string (absolute vs relative).

Range.Address([RowAbsolute],
              [ColumnAbsolute],
              [ReferenceStyle],
              [External],
              [RelativeTo]) AsString

RowAbsolute - True or False, default is True
ColumnAbsolute - True or False, default is True
ReferenceStyle - xlReferenceStyle.xlA1
External - True to return an external reference, default is false
RelativeTo - Range representing a relative to cell. Only relevant when ReferenceStyle = xlR1C1


Range.AddressLocal

This is similar to Address but it returns the address in the regional format of the language of the particular country.




Cells.Replace SearchFormat:=True, ReplaceFormat:=True
lLastRowNo = ActiveCell.End(xlDown).Row


Moving large amounts of data quickly

Dim vArray AsVariant

vArray = Range("A1").Resize(10,10)
Range("H6").Resize(10,10) = vArray

Obtaining the cell reference of the active cell

Dim lrownumber AsLong
Dim icolumnno AsInteger

lrownumber = ActiveCell.Row
icolumnno = ActiveCell.Column
Call MsgBox("The currently active cell is " & lrowno & " , " & icolumnno)

Makes the active cell the top left cell in the window

ActiveCell.Select
ActiveWindow.ScrollColumn = ActiveCell.Column
ActiveWindow.ScrollRow = ActiveCell.Row

Selection

Selection.PasteSpecial Paste:=xlValues
Selection.Copy
Selection.Cut
Set objRange = Selection
Selection.Rows.Count

Looping through all the selected cells

ForEach objCell In Selection.Cells

Next objCell

Range of cells currently selected

ifirstcol = Range(Selection.Address).Column
inoofcols = Range(Selection.Address).Column + Selection.Columns.Count
lfirstrow = Range(Selection.Address).Row
lnoofrows = Range(Selection.Address).Row + Selection.Rows.Count
Selection.Font.Size = 12
If TypeName(Selection) = "Range" Then MsgBox("More than one cell currently selected")

Formatting

Call MsgBox( Range("A3").Font.ColorIndex )
Call MsgBox( Range("A3").Interior.ColorIndex )
If (Range("A3").Font.ColorIndex < 0) ThenCall MsgBox (" When ?")
If (Range("A3").Interior.ColorIndex < 0) ThenCall MsgBox (" When ?")
Range("A2").Font.Bold = True
Range("A4:D10").NumberFormat = "mmm-dd-yyyy"

Looping Through Cells

Dim rgeCell As Cell

ForEach rgeCell In Range("A1:D30").Cells
   rgeCell.Value = 20
Next rgeCell
Dim rgeCurrent As Range

DoWhileNot IsEmpty(rgeCurrent)
'do something
   Set rgeCurrent = rgeCurrent.Offset(1,0)
Loop

You can prevent the user scrolling around a worksheet by defining the scroll area. Worksheets("Sheet1").ScrollArea = "A1:D400". To set the scrolling back to normal just assign the ScrollArea to an empty string. Note that this setting is not saved so it may be necessary to include it in the WorkBook_Open() event procedure.
You can quickly assign an Excel Range of cells to an array and visa-versa. Be aware that these arrays will start at 1 and not 0. vArrayName = Range(---).Value.


Determining a Cell Datatype

Help determine the type of data contained in a cell.
This accepts a range of any size but only operates on the upper left cell in the range.

Function CellType(Rng)
' Returns the cell type of the upper left
' cell in a range
    Application.Volatile
    Set Rng = Rng.Range("A1")
    SelectCaseTrue
        Case IsEmpty(Rng): CellType = "Blank"
        Case Application.IsText(Rng): CellType = "Text"
        Case Application.IsLogical(Rng): CellType = "Logical"
        Case Application.IsErr(Rng): CellType = "Error"
        Case IsDate(Rng): CellType = "Date"
        Case InStr(1, Rng.Text, ":") <> 0: CellType = "Time"
        Case IsNumeric(Rng): CellType = "Value"
    EndSelect
EndFunction

Determining whether a range is contained in another range


Function InRange(rng1, rng2) AsBoolean
' Returns True if rng1 is a subset of rng2
    InRange = False
    If rng1.Parent.Parent.Name = rng2.Parent.Parent.Name Then
        If rng1.Parent.Name = rng2.Parent.Name Then
            If Union(rng1, rng2).Address = rng2.Address Then
                InRange = True
            EndIf
        EndIf
    EndIf
EndFunction


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions LimitedTopPrevNext
Sours: https://bettersolutions.com/excel/cells-ranges/vba-code.htm


61 62 63 64 65