Excel VBA , Range Object, Cells, Rows, Columns, Copy , count Tutorial

Range Object Refferences

(Mengenal Object Range di Excel VBA Macro)

Range Examples | Cells | Declare a Range Object | Select | Rows | Columns | Copy/Paste | Clear | Count
The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA. This chapter gives an overview of the properties and methods of the Range object. Properties are something which an object has (they describe the object), while methods do something (they perform an action with an object).
Range Examples

Place a command button on your worksheet and add the following code line:
Range(“B3”).Value = 2

Excel VBA Range Example

Range(“A1:A4”).Value = 5

Range Example

Range(“A1:A2,B3:C4”).Value = 10


Instead of Range, you can also use Cells. Using Cells is particularly useful when you want to loop through ranges.

Cells(3, 2).Value = 2

Cells in Excel VBA

Explanation: Excel VBA enters the value 2 into the cell at the intersection of row 3 and column 2.

Range(Cells(1, 1), Cells(4, 1)).Value = 5

Declare a Range Object

You can declare a Range object by using the keywords Dim and Set.

Dim example As Range
Set example = Range(“A1:C4”)

example.Value = 8


An important method of the Range object is the Select method. The Select method simply selects a range.

Dim example As Range
Set example = Range(“A1:C4”)



The Rows property gives access to a specific row of a range.

Dim example As Range
Set example = Range(“A1:C4”)



The Columns property gives access to a specific column of a range.

Dim example As Range
Set example = Range(“A1:C4”)



The Copy and Paste method are used to copy a range and to paste it somewhere else on the worksheet.



Although this is allowed in Excel VBA, it is much better to use the code line below which does exactly the same.
Range(“C3:C4”).Value = Range(“A1:A2”).Value


To clear the content of an Excel range, you can use the ClearContents method.

or simply use:
Range(“A1”).Value = “”

Note: use the Clear method to clear the content and format of a range. Use the ClearFormats method to clear the format only.


With the Count property, you can count the number of cells, rows and columns of a range.

Count Property

Note: border for illustration only.

Dim example As Range
Set example = Range(“A1:C4”)

MsgBox example.Count

Count Cells

Dim example As Range
Set example = Range(“A1:C4”)

MsgBox example.Rows.Count

Count Rows

Note: in a similar way, you can count the number of columns of a range.

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Author: Inhouse Trainer freelance

Hery Purnama 081.223344.506 - Freelance Trainer , Excel VBA Macro, Android, Phonegap, ITIL, COBIT, Google Map API, Sketchup3D , MS Project, Sencha ExtJS, Oracle , MySQL DBA, MS. Access VBA, Primavera P6, MS Project ,Jquery, Yii, CodeIgniter. laravel, CATIA 3D, Unity 5 , PMBOK, PMO, TOGAF, PRINCE2 - Call : 081.223344.506 | PinBB : 7DC633AA| Email / YM : inhousetrainer@yahoo.com | FB : inhousetrainer | TW : inhousetrainer

Leave a Reply

Your email address will not be published. Required fields are marked *