VBA basics
9. How to specify cells in Range

Excel macro basics and applications, introduction to Excel VBA
last updated:08-14-2019

9. How to specify cells in Range

Some of this topic was processed by machine translation. Original


How to use Range that came out until the last time
Range ("A1")
It was a case of specifying one cell by writing like this.


When specifying multiple cell ranges,
Rectangular cell range, entire row, entire column, special cell range
Let's see how to specify these.

Multiple cells (rectangular cell range)

How to write VBA when specifying multiple cells (rectangular cell range).
You can select multiple cells by dragging with the mouse.

Range("A1: C5")
In this way, specify the start point and end point by separating them with a: (colon).
It is the same even if it is specified as follows.
Range("A1", "C5")
In this way, specify the start point and end point by separating them with, (comma).
Now you can specify the square cell range (15 cells) from cell A1 to cell C5.

That means


Range(start cell, end cell)
Specify it like this.

Whole columns

How to write VBA when specifying the entire column.
This is the range to select by clicking the column heading.

Range("A: A")
This will make the entire column A.

Range("A: C") or Range("A: A", "C: C")
If this is the case, it will be specified in 3 columns from column A to column C.

Whole rows

This is how to write VBA to specify the whole line.
This is the range to select by clicking the row heading.

Range("1: 1")
This will specify the entire first line.

Range("1: 5") or Range("1: 1", "5: 5")
In this case, 5 lines from 1 to 5 lines are specified.

cell range of defined name

How to write VBA for a cell with a defined name.
For cell ranges whose names are selected from the name box.

Range("Name of name definition")
This can be specified.

Special specification method

Away cell

This is how to write VBA when specifying away cells.

Specify multiple cells as you select with the mouse while pressing Ctrl.

Range("A1, C5")
In this specification, only A1 cell and C5 cell (2 cells) are targeted.
Specify in "(double quotations) separated by, (comma).
You can specify any number of cells in this case, but there is an upper limit.

Crossing cell
As a very special specification method,
You can specify intersecting cells with whitespace separation.

Range("A: C 1: 5")
This is the intersection of A: C and 1: 5,
In other words, it is a rectangular cell range of A1 to C5.

This designation is
Since it is the same as Range ("A1", "C5"), you will rarely use it.

How to use frequently in macro VBA

About how to write Range
I introduced various ways of writing in the second half.

Range("A1")
Range("A1: C5")
Range("A: A")
Range("1: 1")
Range("Name of name definition")

One of the above will be almost.

And finally,
Be sure to write the cell address in the upper case in the Range.
Sheet functions are converted to upper case even if written in lower case, but not in macros.
After all, it should be easy to see the same capitalization as the sheet function.



Articles with the same theme "VBA basics"

6. Let's write it anyway (Sub, End Sub)
7. To run a macro (F5)
8. What is putting a character in a cell (Range, Value)
9. How to specify cells in Range
10. Specification method other than Range (Cells, Rows, Columns)
11. How to use Range and Cells
12. Variables and data types (Dim)
13. Constants and type declaration characters (Const)
14. Character concatenation and continuation line
15. Four arithmetic operations and annotations (comments)
16. Iterative processing (For Next)



  • >
  • >
  • 9. How to specify cells in Range

  • If you find this site useful, please share and bookmark.


    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.




    If you find this site useful, please share and bookmark.
    To the bottom of the text