Excel range function

Excel range function DEFAULT

How to Find Range in Excel (Easy Formulas)

Normally, when I use the word range in my tutorials about Excel, it’s a reference to a cell or a collection of cells in the worksheet.

But this tutorial is not about that range.

A ‘Range’ is also a mathematical term that refers to the range in a data set (i.e., the range between the minimum and the maximum value in a given dataset)

In this tutorial, I will show you really simple ways to calculate the range in Excel.

What is a Range?

In a given data set, the range of that data set would be the spread of values in that data set.

To give you a simple example, if you have a data set of student scores where the minimum score is 15 and the maximum score is 98, then the spread of this data set (also called the range of this data set) would be 73

Range = 98 – 15

‘Range’ is nothing but the difference between the maximum and the minimum value of that data set.

How to Calculate Range in Excel?

If you have a list of sorted values, you just have to subtract the first value from the last value (assuming that the sorting is in the ascending order).

But in most cases, you would have a random data set where it’s not already sorted.

Finding the range in such a data set is quite straightforward as well.

Excel has the functions to find out the maximum and the minimum value from a range (the MAX and the MIN function).

Suppose you have a data set as shown below, and you want to calculate the range for the data in column B.

Dataset to find Range in Excel

Below is the formula to calculate the range for this data set:

=MAX(B2:B11)-MIN(B2:B11)

Formula to calculate range in Excel

The above formula finds the maximum and the minimum value and gives us the difference.

Quite straightforward… isn’t it?

Calculate Conditional Range in Excel

In most practical cases, finding the range would not be as simple as just subtracting the minimum value from the maximum value

In real-life scenarios, you might also need to account for some conditions or outliers.

For example, you may have a data set where all the values are below 100, but there is one value that is above 500.

If you calculate arrange for this data set, it would lead to you making misleading interpretations of the data.

Thankfully, Excel has many conditional formulas that can help you sort out some of the anomalies.

Below I have a data set where I need to find the range for the sales values in column B.

Dataset to find Range in Excel

If you look closely at this data, you would notice that there are two stores where the values are quite low (Store 1 and Store 3).

This could be because these are new stores or there were some external factors that impacted the sales for these specific stores.

While calculating the range for this data set, it might make sense to exclude these newer stores and only consider stores where there are substantial sales.

In this example, let’s say I want to ignore all those stores where the sales value is less than 20,000.

Below is the formula that would find the range with the condition:

=MAX(B2:B11)-MINIFS(B2:B11,B2:B11,">20000")

Calculating conditional range in Excel

In the above formula, instead of using the MIN function, I have used the MINIFS function (it’s a new function in Excel 2019 and Microsoft 365).

This function finds the minimum value if the criteria mentioned in it are met. In the above formula, I specified the criteria to be any value that is more than 20,000.

So, the MINIFS function goes through the entire data set, but only considers those values that are more than 20,000 while calculating the minimum value.

This makes sure that values lower than 20,000 are ignored and the minimum value is always more than 20,000 (hence ignoring the outliers).

Note that the MINIFS is a new function in Excel is available only in Excel 2019 and Microsoft 365 subscription. If you’re using prior versions, you would not have this function (and can use the formula covered later in this tutorial)

If you don’t have the MINIF function in your Excel, use the below formula that uses a combination of IF function and MIN function to do the same:

=MAX(B2:B11)-MIN(IF(B2:B11>20000,B2:B11))

Just like I have used the conditional MINIFS function, you can also use the MAXIFS function if you want to avoid data points that are outliers in the other direction (i.e., a couple of large data points that can skew the data)

So, this is how you can quickly find the range in Excel using a couple of simple formulas.

I hope you found this tutorial useful.

Other Excel tutorials you may like:

Sours: https://trumpexcel.com/find-range-in-excel/

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/
  1. 2011 kia optima
  2. Chase online business
  3. Qualtrics ohio university
  4. Comfort suites central tulsa
  5. Wells fargo phone

Excel Function for Range

Excel Function for Range

Excel Function for Range (Table of Content)

Range Function in Excel

Range in Excel is the difference between the maximum limit and minimum limit of the available numbers in excel. For example, we have around 10 different number of randomly selected in a list in Excel. To calculate the Range for these numbers, we need to find the upper and lower values using the MAX and MIN function in the list of those cells. Once we get the maximum and minimum values out of those numbers, then subtract the Max value from the Min value. The returned number will be the range.

There are two kinds of ranges used extensively in excel, which are illustrated below:

Symmetrical Range: A range which consists of all cells in adjacent positions to each other. Such a range usually shows up as a square or rectangle in a spreadsheet when highlighted. The range shown in the image would be (A1:C4)

Excel function for range 1

Irregular Range: A range consisting of cells that are not contiguous and may not have regular geometrical shapes when highlighted. The range highlighted in the image would be (A1:C4, E1,E4, B6,A7)

Excel function for range 2

Examples of Range Function in Excel

Now, a range in itself would not be useful as we have to derive insights from the range’s data. So formulae are used with cell ranges which add the operation we want to perform in the data from the range. For example, if we want to find the sum of the numbers in cells A1 till C4, .we would use =SUM(A1:C4)

Excel function for range 3

Example #1 – Finding Maximum and Minimum

1) Finding the maximum and minimum values in a cell range: We use the following functions when we are looking for minimum and maximum values in a cell range. Please note that this would give us the mathematical result and not the maximum and minimum as defined by cell number.

  • For Maximum: We would use the =MAX(Cell Range) function as illustrated below.

Finding Maximum and Minimum example 1.1

  • For Minimum: We would use the =MIN(Cell Range) function as shown below.

Finding Maximum and Minimum example 1.2

2) Suppose we are not interested just in the minimum and maximum in the highlighted range, but also in the top and bottom k numbers in the range. We can use the following functions to calculate those.

  • For the top k number, say k=3, which means the third-largest number in the range, we would use the function =LARGE(Cell Array,k) for symmetrical ranges or =LARGE((Cell Range),k) for irregular ranges as shown below.

Finding Maximum and Minimum example 1.3

Finding Maximum and Minimum example 1.4

  • A very similar function for finding the kth smallest number in a range would be to use =SMALL(Cell Array, k) for a symmetrical range or =SMALL((Cell Range),k) for an irregular range.

Finding Maximum and Minimum example 1.5

Finding Maximum and Minimum example 1.6

Example #2 – How to Define and Use Ranges in Excel?

We will now look at how to define and use ranges in excel. First, we need to have data to work with. This can be anything in a spreadsheet ranging from letters to numbers or a combination of both. For the illustrations accompanying this discussion, I am using a sample from a production database which stores data on how many parts are produced in a year.

Use Ranges in Excel Example 2.1

Let us say we want to know the range of production numbers that have been entered over the two years. We do this by subtracting the minimum from the maximum value. For this, we need not find each value individually but have to breakdown the calculation steps and write the formula as follows:

MAX(Cell Range)-MIN(Cell Range)

Please note that the cell range has to be the same in the arguments; otherwise, the formula would not return the correct result.

Use Ranges in Excel Example 2.2

We find that the range of production is 368 parts. Now, if we want to find out the occurrence of a particular value in the range, or a range of values within the range, we use another function called COUNTIF. This function has the following syntax:

COUNTIF(Range, value)

Let us suppose we want to find what was month in which we hit more than 300 parts. The formula would be =COUNTIF(C2:C23,”>300″)

Use Ranges in Excel Example 2.3

We find that for 7 months, the production was more than 300 parts. We can also find out if we had any month which was below a particular number, suppose 100. We would use a nested COUNTIF formula within an IF statement to get a Yes or No answer like this:

=IF(COUNTIF(range,” value”),” Yes”,” No”)

This would look like this:

Use Ranges in Excel Example 2.4

The result would be a No as none of the production numbers in the range is below 100. A variation of this can be used to find if we have any production number in a particular value. This would be as follows:

COUNTIF(range,”*”&value&”*”) or COUNTIF(range, value)

The first variation is useful if we want to match two different databases, and the second when we want to find out if a particular value occurs or not, and if it does, then how many times it reoccurs.

Use Ranges in Excel Example 2.5

Use Ranges in Excel Example 2.6

We can use the MATCH function instead of COUNTIF in case we want to find the number of values greater or less than a given value.

Use Ranges in Excel Example 2.7

In the above example, we use the MATCH function to find the number of months that had less than 300 parts produced.

Things to Remember

  • We should sort the data in ascending or descending order wherever feasible to simplify operations when using ranges.
  • Quotation marks (“”) and asterisk(*) are used in formulae whenever we are looking for substrings or specific text ranges within a range.
  • Irregular ranges are the most common ranges used in business. As such, whenever possible, we should use tables to classify the data before running any operations on them.
  • It must be noted that ranges can be highlighted manually, and Excel displays the number of cells in it as a count at the bottom; however, we can find out the number of rows or columns in a range using the following functions:

ROWS(range)
COLUMNS(range)

Usually, these two functions are not required but are useful for large tables and multiple databases and for recording macros.

Conclusion

Knowledge of range in excel is an important pre-requisite to being able to manipulate data. The range is also used in recording macros and VBA coding, and hence an in-depth understanding of range is a must for anyone using excel.

Recommended Articles

This is a guide to Excel Function for Range. Here we have discussed Examples of Range Function in Excel along with steps and a downloadable excel template. You may also look at the following articles to learn more –

  1. VBA Range
  2. VBA Selecting Range
  3. Excel Named Range
  4. Range in Excel
Sours: https://www.educba.com/excel-function-for-range/

Define range based on cell value

This formula relies on on a specific behavior of INDEX - although it seems that INDEX returns the value at a particular location, it actually returns the reference to the location. In most formulas, you wouldn't notice the difference – Excel simply evaluates the reference and returns the value. This formula uses this feature to construct a dynamic range based on worksheet input.

Inside the sum function, the first reference is simply the first cell in the range that covers all possible cells:

To get the last cell, we use INDEX. Here, we give INDEX the named range "data", which is the maximum possible range of values, and also the values from J5 (rows) and J6 (columns). INDEX doesn't return a range, it only returns a single cell at that location, E9 in the example:

INDEX(data,J5,J6)// returns E9

The original formula is reduced to:

which returns 300, the sum of all values in C5:E9.

The formula in J8 is almost the same, but uses AVERAGE instead of SUM to calculate an average. When a user changes values in J5 or J6 the range is updated, and new results are returned.

Alternative with OFFSET

You can build a similar formulas with the OFFSET function, shown below:

OFFSET is designed to return a range, so the formulas are perhaps simpler to understand. However, OFFSET is a volatile function, and can cause performance problems when used in larger, more complex worksheets.

Sours: https://exceljet.net/formula/define-range-based-on-cell-value

Range function excel

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 Calculate the Range in Excel (in 3 easy steps)

Range in Excel

Find Range in Excel

Find Range in Excel (Table of Contents)

Range in Excel

Whenever we talk about the range in excel, it can be one cell or can be a collection of cells. It can be the adjacent cells or non-adjacent cells in the dataset.

What is Range in Excel & its Formula?

A range is the collection of values spread between the Maximum value and the Minimum value. A range is a difference between the Largest (maximum) value and the Shortest (minimum) value in a given dataset in mathematical terms.

Range defines the spread of values in any dataset. It calculates by a simple formula like below:

Range = Maximum Value – Minimum Value

How to Find Range in Excel?

Finding a range is a very simple process, and it is calculated using the Excel in-built functions MAX and MIN. Let’s understand the working of finding a range in excel with some examples.

Range in Excel – Example #1

We have given below a list of values:

23, 11, 45, 21, 2, 60, 10, 35

The largest number in the above-given range is 60, and the smallest number is 2.

Thus, the Range = 60-2 = 58

Explanation:

  • In this above example, the Range is 58 in the given dataset, which defines the span of the dataset. It gave you a visual indication of the range as we are looking for the highest and smallest point.
  • If the dataset is large, it gives you the widespread of the result.
  • If the dataset is small, it gives you a closely centered result.

A process of defining the range in Excel

For defining the range, we need to find out the maximum and minimum values of the dataset. In this process, two function plays a very important role. They are:

  1. MAX
  2. MIN

Use of MAX function:

Let’s take an example to understand the usage of this function.

Range in Excel – Example #2

We have given some set of values:

Example 2

For finding the maximum value from the dataset, we will apply here the MAX function as below screenshot:

Range in Excel Example 2-2

Hit enter, and it will give you the maximum value. The result is shown below:

Range in Excel Example 2-3

Range in Excel – Example #3

Use of MIN function:

Let’s take the same above dataset to understand the usage of this function.

Example 3

For calculating the minimum value from the given dataset, we will apply the MIN function here as per the below screenshot.

Range in Excel Example 3-2

Press ENTER key, and it will give you the minimum value. The result is given below:

Range in Excel Example 3-3

Now you can find out the range of the dataset after taking the difference between Maximum and Minimum value.

We can reduce the steps for calculating the range of a dataset by using the MAX and MIN functions together in one line.

For this, we again take an example to understand the process.

Range in Excel – Example #4

Let’s assume the below dataset of a company employee with their achieved sales target.

Example 4

Now for identifying the span of sales amount in the above dataset, we will calculate the range. For this, we will follow the same procedure as we did in the above examples.

We will apply the MAX and MIN functions for calculating the Maximum and Minimum sales amount in the data.

For finding the range of sales amount, we will apply the below formula:

Range = Maximum Value – Minimum Value

Refer to the below screenshot:

Range in Excel Example 4-2

Press Enter key, and it will give you the range of the dataset. The result is shown below:

Range in Excel Example 4-3

As we can see in the above screenshot, we applied the MAX and MIN formulas in one line, and by calculating their results difference, we found the range of the dataset.

Things to Remember

  • If the values are available in the non-adjacent cells, you want to find out the range; you can pass the cell address individually, separated with a comma as an argument of the MAX and MIN functions.
  • We can reduce the steps for calculating the range by applying MAX and MIN functions in one line. (Refer to Example 4 for your reference)

Recommended Articles

This has been a guide to Range in Excel. Here we discuss how to find Range in Excel along with excel examples and downloadable excel template. You may learn more about Excel from the following articles –

  1. Excel Function for Range
  2. Excel Named Range
  3. VBA Range
  4. VBA Selecting Range
Sours: https://www.educba.com/range-in-excel/

You will also like:

In addition, such a relaxation in Asimov's laws is quite logical. After all, if it is necessary to save human life (that is, to fulfill the first law), but it will be necessary. Say, to amputate a person's hand, then artificial intelligence will face a dilemma.



730 731 732 733 734