Excel Made Easy

Vba range object in Excel

As discussed in previous chapters, a Range is nothing but a collection of Cells within a Sheet.

The Range object is always used along with the Sheets object.

The contents of a sheet can be acessed easily with help of Range object. To do this, it is simply enough to know the address of the cell.

The Range object can be used to either retrive a value from a cell or group of cells, or to write new value in a cell or group of cells.

Simply put, A range represents a cell,row,column or a continous group of cells of any dimension.

The various functionalities and properties of Range object are as discussed below.

Value

The Value property of the range object is used to either get or set value in a cell.

This property is optional as illustrated below in the example

Clear

This method is used to clear a cell or group of cells of its contents.

There are various forms of this method such as ClearContents, Clearformats and so on.

Sort

This property is used to sort a range, usually along a column or row in ascending or descending order.

Formula

This method is used to specify a formula in a cell or group of cells.

The formula expression starts with "=".

Apart from the above mentioned methods and properties there are various other members of range object,

which are very much useful in programming.

The following example illustrates the range object

  1. Sub range_demo()
  2. Dim sht As Worksheet
  3. Set sht = Sheets.Add
  4. sht.Name = "WS"
  5. sht.Activate
  6. MsgBox "Input value into range A1:A5"
  7. sht.Range("A1") = 4
  8. sht.Range("A2") = 2
  9. sht.Range("A3") = 7
  10. sht.Range("A4") = 1
  11. sht.Range("A5") = 5
  12. MsgBox "Sort range A1;A5 in A to Z"
  13. MsgBox "Ready?"
  14. sht.Range("A1:A5").Sort key1:=sht.Range("A1"), order1:=xlAscending, Header:=xlNo
  15. MsgBox "Now Sorting range A1;A5 in Z to A"
  16. MsgBox "Ready?"
  17. sht.Range("A1:A5").Sort key1:=sht.Range("A1"), order1:=xlDescending, Header:=xlNo
  18. MsgBox "Get value from Cell A4"
  19. MsgBox "Value of A4 : " & sht.Range("A4").Value
  20. MsgBox "That's it!"
  21. sht.Activate
  22. sht.Delete
  23. End Sub

Editor Screenshot

excel vba range object

 

You can find similar Excel Questions and Answer hereunder

1) How can I set FreezePanes in a certain range using VBA?

2) What is a methods. Methods are action that can be performed by an object

3) How can I clear all formats (formats alone not data) from a range of cells?

4) How can I fill a range of cells with incremental times?

5) How can I find the sum of records that meet a given condition in my raw data table?

6) How can I quickly remove all blank cells in a data range?

7) How can I add a Prefix to a set of numbers in a range?

8) How to add email and emailing function in Excel VBA

9) What are objects and what are properties in Excel VBA

10) Is there a way I can average a range of numbers even if there is an error value in range?

 

Here the previous and next chapter

Excel Made Easy was founded in 2002. Excel Made Easy is a service to people looking to learn Microsoft Excel or any other spreadsheet SW on the market.

We endeavor to provide the best service and support and to respond quickly to your questions.

Excel Made Easy is a proud sponsor of Dismantle-It.com the site that shows you the belly of your daily objects, Poupounette.com the site of cars loving cat and PowerPointMadeEasy.com the world top reference for PowerPoint Presentations questions and Pet Cemetery Online a site dedicated to your loving Pets.