How to refer to Cells and Ranges by using A1 notation (VBA)

You want to change value of many / more than one cells at once? You will use range property to do that.

Range properties is great and flexible meaning: Range can be used to refer 1 cell, and also many cells (a block).

You can refer to a cell, for instance we targeting cell A1's value:
sheet1.range("A1").value
we refer to cell A1's value that contained within.

And, you can also refer to many cells (more than 1 cell), for instance:
sheet.range("A1:A2").value
we refer to cell A1's and cell A2's value contained.

Essentially, you should help / navigate Range property with A1 notation.

What is A1 notation? A1 notation could be: A1, A1:A2, A:A, 1:1, A1:D5
A1 means: cell 1 column A
A1:A2 means: cell 1 column A and cell 2 column A
A:A means all cells in column A
1:1 means all cells in row 1
A1:D5 means a block start from A1 to D5. This kind a awkward, but try that on your VBA.


To recap all: in short explanation use this syntax: sheet.range("A1_Notation") and A1 notation could be: A1, A1:A2, A:A, 1:1, and A1:D5


So now try to combine/write them on your VBA editor:
Sheet1.range("A1").value
Sheet.range("A1:A2").value
Sheet.range("A:A").value
Sheet.range("1:1").value
Sheet.range("A1:D5").value

You are done!

PS: Did you notice that Ms. Excel is indexing his row and column with numeric (1, 2, 3, 4,...) and alphabet (A, B, C, D...). So, in referencing a cell in A1 notation, we must use this convention.

Quiz:
1. How could you refer to a block of cells/matrix (3 x 3) that start from D5?
Clue: Sheet1.Range("Start cell : End cell")

2.  Try this sheet.range("A:A, E6:H9").value = 10
What you see?