Calculating Range References (Excel (All))

Topic

New Reply

Very nice – just one small observation I would make:
When assigning a range to a variable, it is best to specify the sheet (and ideally, the workbook) that the range belongs to rather than relying on the default activesheet. That way you can be sure of exactly what you are dealing with.

Reply | Quote

Replies

Tx Rory. It is good that you mention this for any others who may find this post useful. I myself will take that into consideration.
Cheers

Reply | Quote

(Edited by Rudi on 24-Dec-07 02:08. Added some more threads with additional code to select ranges.)

Calculating Range References

One of the most valuable techniques in Excel programming is the ability to calculate a range reference, store it in a declared variable and then use that reference to complete tasks in the worksheet. Think of setting a range reference as the “hard work” done at the beginning of the macro, and once set, you can do anything with that reference. If the set range reference is calculated in such a way to be volatile, then it really does make a flexible and dynamic macro, irrespective of what it is programmed to do on the worksheet.

In this post I have compiled a variety of ways to accurately calculate the required range reference using range properties or functions to define the reference. The details below are to serve as examples that you can use or modify to suite your requirements.

For ALL examples below you need to set up a declared variable(s) by “DIM’ing” it as a range object. After that you SET the variable(s) with the appropriately calculated reference. The most basic example I can provide is as follows:

Dim myRange as Range
Set myRange = Range(“A1”)

The above example creates a reference to Range A1 and populates the variable called myRange, (declared as a range type variable), with that reference. “myRange” can now be used anywhere within code to refer to Range A1. But this is just the beginning. The range object can be used to calculate a myriad of useful references to specific or volatile areas on your worksheet using properties or functions.

Below follow more examples for your reference. Once you have the range reference set, you can select the range using the statement : myRange.Select in each case below

Using CurrentRegion
Set myRange = Range(“A1”).CurrentRegion
CurrentRegion is a property of the range object that extents the selection (from the initial reference) into all directions to create a solid selection encompassing a list of information block of various sizes.

Using Resize
Set myRange = Range(“A1”).CurrentRegion.Resize(1)
Resize resizes the selection to become only one row in height. This is perfect for selecting all the column headings of a list of any number of columns in width.

Set myRange = Range(“A1”).CurrentRegion.Resize( ,1)
Resizes the selection to become only one column in width. This is perfect for selecting all the column headings of a list of any number of columns in width.

Set myRange = Range(“A1”).CurrentRegion.Resize(Range(“A1”).CurrentRegion.Rows.Count+1 , Range(“A1”).CurrentRegion.Columns.Count+1)
Selects the entire list including an extra row and column below and to the right.

Using Offset
Set myRange = Range(“A1”).CurrentRegion.Offset(1,1)
Unlike Resize, which actually resizes the selection to a new dimension, Offset moves the selection up, down, left or right. The example above selects the entire list and offsets the selection one row down and one column to the right. It theirfore excludes the first row and first column.

Using Offset and Resize
Set myRange = Range(“A1”).CurrentRegion.Offset(1,1).Resize(Range(“A1”).CurrentRegion.Rows.Count-1 , Range(“A1”).CurrentRegion.Columns.Count-1)
The example above selects the entire list and offsets the selection one row down and one column to the right. Then Resizes the selection to exclude the extra blank row and column resulting from the offset.

Set myRange = Range(“A1”).CurrentRegion.Offset(Range(“A1”).CurrentRegion.Rows.Count,0).Resize(1)
The example above selects only the row directly below the list. A perfect position to insert totals if required.

Using the Union Function
Set myTitles = Range(“A1”).CurrentRegion.Resize(1)
Set myTotals = Range(“A1”).CurrentRegion.Offset(Range(“A1”).CurrentRegion.Rows.Count,0).Resize(1)
Set myRange = Union(myTitles,myTotals)
You can use the Union Function to select multiple ranges in one go. Union has 30 arguments allowing you to select up to 30 selections using one variable. The above example selects the Titles and the Totals rows.

Using the Intersect Function
Set myRng1 = Columns(“D”).EntireColumn
Set myRng2 = Rows(10).EntireRow
Set myRange = Intersect(myRng1,myRng2)
The Intersect Function selects the cells that represent the rectangular intersection of two or more ranges. The function has up to 30 arguments that (if used) will select only cells that have been intersected by a row and column.

Set myRng1 = Range(“A1”).CurrentRegion.Offset(1, 0)
Set myRng2 = Range(“A1”).CurrentRegion.Offset(0, 1)
Set myRange = Intersect(myRng1, myRng2)
This example selects the body info of a data block. In other words, it selects the entire data block excluding the first row and first column. Another version of an example above.

Conditional Selection
I tracked down a question I posted some while back that shows how to invert a selection. The example is rather specific to duplicate entries, but can be modified to suite requirements you may have. See post 485,599 for details.

Inverting the Selection within a range
Hans has graciously provided some handy code to invert the selection based on current selected cells. This takes range calculation to the extreme (in my opinion). For this very useful code, see post 684,556 for details.

Using the Find Command
Set myRange = Cells.Find(What:=”Type Search Value Here”, After:=Cells(1), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
myRange.Select
Using the Find command can help find a specific entry on the worksheet. Once that unique entry is located, a reference to that cell is created and then you can use the above techniques to extend the selection with CurrentRegion, Offset, Resize, etc…

If you find these techniques to select ranges useful, I can recommend ASAP Utilities. As quotes from the site : ASAP Utilities is a powerful Excel add-in that fills the gaps in Excel and automates frequently used tasks. Since 1999 it has grown to become probably one of the world’s most popular add-ins for MS Excel. This add-in provides all the above options for selecting ranges, and MORE, all compiled into a tool that neatly integrates into Excel in a custom menu. Selecting specific areas in the worksheet is now just a menu command away. See the site and the downloads here.

Reply | Quote

Thanks Rudi, this will be useful to many readers.

Two other remarks:

1) An instruction such as

Set myRange = Range(“A1”).CurrentRegion.Offset(1,1).Resize(Range(“A1”).CurrentRegion.Rows.Count-1 , Range(“A1”).CurrentRegion.Columns.Count-1)

is rather long. You can split it into two instructions:

Set myRange = Range(“A1”).CurrentRegion
Set myRange = myRange.Offset(1,1).Resize(myRange.Rows.Count-1 , myRange.Columns.Count-1)

to improve readability.

2) (This is for others reading this, I know that you know it)
Your post uses “smart quotes”, for example in Range(“A1”). VBA doesn’t understand those, you should always use straight quotes: Range(“A1”).

Reply | Quote

Thats eagle eys Hans. I did not even notice that. Sorry about it…I actually typed the entire thing in Word!
I changed those quotes and it is now VBA ready

Reply | Quote

If they copy and paste, the code will fail – VBA will assume that “A1” (including the smart quotes) is the name of a non-existent variable.

Reply | Quote

TX for the warning. I have modified the quotes so that the data can be used in VBA modules.

Reply | Quote