{"id":26,"date":"2014-01-24T16:04:03","date_gmt":"2014-01-24T23:04:03","guid":{"rendered":"https:\/\/blogs.ubc.ca\/coetoolbox\/?page_id=26"},"modified":"2016-09-26T09:45:07","modified_gmt":"2016-09-26T16:45:07","slug":"ranges","status":"publish","type":"page","link":"https:\/\/blogs.ubc.ca\/coetoolbox\/vba\/ranges\/","title":{"rendered":"Working with Ranges"},"content":{"rendered":"

A Range object can represent a single cell, a range of cells, an entire row or column, or a selection containing multiple ranges.<\/p>\n

Note that if a worksheet is not specified, VBA will assume that the cells are in the active sheet, which is fine if your code is attached to that specific sheet. If you’re working with multiple worksheets in the same module, remember to define all of your ranges specifying a worksheet.<\/p>\n

Here are some of the more important methods that we use to work with ranges:<\/p>\n

1. Setting range variables<\/strong>: use the Set statement to set range variables:<\/p>\n

Dim myRange as Range
\nSet<\/span> myRange = Range(“A1”)<\/p>\n

You can then refer to the range by name, instead of by cell.<\/p>\n

myRange<\/span>.Value=”hello world”<\/p>\n

2. Range method<\/strong>: returns a range object. The argument is a string that\u2019s either an A1-style reference or the name of a range that was defined in Excel. To name a range in Excel, select the range, right click, and choose Define name.<\/p>\n

\"snip\"<\/a><\/p>\n

Worksheets(“Sheet1”).Range(“A1”)<\/span>.Value=3
\nRange(“A1:A3”)<\/span>.Value=4
\nRange(“range3”).Formula = “=5*RAND()”<\/p>\n

3. Cells method<\/strong>: is similar to the Range method, but takes numeric arguments instead of string arguments. Note that the values are (row, column)–this might feel weird because it’s sort of like (y,x) instead of (x,y).<\/p>\n

Worksheets(“Sheet1”).Cells<\/span>(1,1).Value=3
\nCells<\/span>(1,1).Formula=”=10*RAND()”<\/p>\n

You can combine the range and cells methods to return a region. The following statement selects cells A1:C4<\/p>\n

Range<\/span>(Cells<\/span>(1,1), Cells<\/span>(3,4)).select<\/p>\n

3. Offset method<\/strong>: takes an input Range object, and RowOffset and ColumnOffset arguments, returning a new range. This is particularly handy in combination with the “with” programming structure: you can refer to multiple offset values in a compact way.<\/p>\n

Range(\u201cA1\u201d).Offset<\/span>(0,1).Value = 2
\nWith Range(\u201cA1:B5\u201d)
\n.Offset<\/span>(0,1).Value = 2
\n.Offset<\/span>(0,2).Value = “hello”
\nEnd With<\/p>\n

4. End property<\/strong>: returns the last non-empty cell in a column\/row:<\/p>\n

ActiveCell.End<\/span>(xlDown<\/span>)
\nRange(ActiveCell, ActiveCell.End<\/span>(xlDown<\/span>))<\/p>\n

Other directions: xlUp<\/span>, xlToLeft<\/span> and xlToRight<\/span><\/p>\n

5. CurrentRegion property<\/strong>: returns the range of cells bounded by empty rows and empty columns<\/p>\n

Range(“A1”).CurrentRegion<\/span>.select<\/p>\n

6. UsedRange property<\/strong>: returns the rectangular range that contains every nonempty cell of the worksheet.<\/p>\n

Set myRange=Worksheets(“Sheet1”).Range(“A1”).UsedRange<\/p>\n

7. Union method<\/strong>: create a new range by referring to multiple other ranges.<\/p>\n

Union(Range(“A1”).CurrentRegion, Range(“A21”)).select<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

A Range object can represent a single cell, a range of cells, an entire row or column, or a selection containing multiple ranges. Note that if a worksheet is not specified, VBA will assume that the cells are in the active sheet, which is fine if your code is attached to that specific sheet. If […]<\/p>\n","protected":false},"author":22982,"featured_media":0,"parent":2,"menu_order":4,"comment_status":"open","ping_status":"closed","template":"page-templates\/full-width.php","meta":{"footnotes":""},"class_list":["post-26","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/26","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/users\/22982"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/comments?post=26"}],"version-history":[{"count":8,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/26\/revisions"}],"predecessor-version":[{"id":1498,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/26\/revisions\/1498"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/2"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/media?parent=26"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}