Results 1 to 7 of 7
  1. #1
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calculating Range References (Excel (All))

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating Range References (Excel (All))

    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
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating Range References (Excel (All))

    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").

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating Range References (Excel (All))

    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.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating Range References (Excel (All))

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Calculating Range References (Excel (All))

    <P ID="edit" class=small>(Edited by Rudi on 24-Dec-07 02:08. Added some more threads with additional code to select ranges.)</P>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 "DIMing" 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").Curre ntRegion.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").Curre ntRegion.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").Curre ntRegion.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>post 485,599</post#> 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>post 684,556</post#> 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.
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating Range References (Excel (All))

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •