VBA basics
10. Specification method other than Range (Cells, Rows, Columns)

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

10. Specification method other than Range (Cells, Rows, Columns)

Some of this topic was processed by machine translation. Original


You can specify any cell and range by specifying Range.
When used in a macro, it may be a little difficult to use.



It is inconvenient to specify with characters such as "A1" and "B5".
There are more program-like (laughs) designation methods.

For that,
Cells, Rows, Columns
Use these.

Cells(row number, column number)

One cell or single cell is specified.

Cells(row number, column number)
The line number is the vertical line position, 1 for the first line and 10 for the 10th line.
The column number is the horizontal column position, 1 for column A and 10 for column J.


Column numbers such as "A" and "J" can be specified for column numbers.
In particular,
Cells(1, 1) or Cells(1, "A")
This will be cell A1.
Cells(5, 3) or Cells(5, "C")
This will be a C5 cell.

Compared with Range ("C5"), the matrix order is reversed.
You may get misled at first, but you need to get used to it.
In Cells, the line number comes first.

Multiple cells(cell range) cannot be specified in Cells.

Rows(row number)

When specifying the entire line.

Rows
The line number is the vertical line position, 1 for the first line and 10 for the 10th line.

Rows(5)
This will be the entire fifth line.
Write the numerical value without using "(double quotation)".

Rows("1: 5")
If you specify, it will be in the range of 5 lines from 1 to 5 lines.

Enclose with "(double quotation).

If there is only one line, write the number as it is.
Separate multiple lines with a: (colon) and enclose them in double quotes.

Columns(column number)

When specifying the entire column.

Columns
The column number is the horizontal column position, 1 for column A and 10 for column J.

Columns(3)
This is the third column, the entire C column.
Write the numerical value without using "(double quotation)".

Like Cells,
Columns("C")
In this way, you can also specify column symbols.

For multi-column ranges,
Columns("A: C")
This will specify 3 columns from column A to column C.

If there is only one column, either write the number as it is or specify the column symbol enclosed in "(double quotations).
If there are multiple columns, delimit them with: (colon) and enclose them in double quotes.

Related articles on Range and Cells

How to use Range and Cells
In VBA there are Range and Cells as a way to specify cells. Both Range and Cells specify cells with a Range object. Which should I use? How should we use them properly? In the actual macro VBA description it is necessary to use Range and Cells properly.
Range and Cells
Don't be fooled! Use of Range and Cells properly!
How to use Range: Select up to the last line as an example



Articles with the same theme "VBA basics"

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)
17. Iterative processing (Do Loop)



  • >
  • >
  • 10. Specification method other than Range (Cells, Rows, Columns)

  • 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