VBA basics
11. How to use Range and Cells

Excel macro basics and applications, introduction to Excel VBA
last updated:09-01-2019

11. How to use Range and Cells

Some of this topic was processed by machine translation. Original


VBA Range 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.
I will explain how to use Range and Cells.

In the following, let's look at how to write according to the scene to be used, from basic usage.

Basic usage of Range and Cells

To use Range
For fixed position cells
For cell range (multiple cells)
When using name definitions

It will be one of the above.
When using a variable to specify a single cell (single cell), use Cells.

Using Cells
When specifying a single cell

Cell ranges and name definitions that are multiple cells cannot be specified in Cells.

Specifying fixed cells (fixed positions)

As I wrote at the beginning, if there is no change depending on the condition at the fixed position,

Range("B2")
Range("A1:C5")
Range("1:5")
Range("A:C")

You should write like this.

You can also use name definitions
Range ("Name of definition name")

If you use this name definition, you can specify it only in Range.


Avoid using variables in Range

I sometimes see this way of writing,

Range("A" & i)
i is a variable that contains a numeric value.
(The variables will be explained next time.)

Avoid such usage as much as possible.
To be clear, it is unsightly.
Unsightly means that it is difficult to see.

And the execution speed is also a little slow.
In other words, there is no specific reason for such a description.
There are Cells, so let's basically use this.

Although,
As a scene that uses a variable for Range,
Is it the case that the end of the range is in a variable, not in an iterative process?
The variable, lastRow, contains the last number of rows, and you want to set something in that range.

With Cells
Range(Cells(2, 1), Cells(lastRow, 1)) = ""
Range(Cells(2, 3), Cells(lastRow, 3)) = ""

If you write just Range,
Range("A1:A" & lastRow) = ""
Range("C1:C" & lastRow) = ""

I think this is the case.
In particular, it will come out later, but when you specify a sheet for Range and Cells,
I think that you can use it because writing in Range is much shorter.

When specifying one cell (change the position with VBA)

The basic way to write a single cell that changes its position in VBA is

Cells(row, column)

Please write like this.
Columns can use column symbols as well as numbers.

Cells(5, 3)
Cells(5, "C")

Whichever you write, it will be the same C5 cell.

*While the above is written with a fixed value, macro VBA often specifies this numerical value as a variable.

Assuming that variable i contains a number,
Cells(5, i)
Write like this:

When specifying a cell range (multiple cells)

The basic writing method for a range of cells (multiple cells) is

Range(start cell, end cell)
Specify Cells for the start and end cells.

Range(Cells(row, column), Cells(row, column))
Write like this:

For example,
From cell A1 to cell A5
Range(Cells(1, 1), Cells(5, 1))
From cell A1 to cell C1
Range(Cells(1, 1), Cells(1, 3))
From cell A1 to cell C5
Range(Cells(1, 1), Cells(5, 3))
It looks like this.

* While the above is written with a fixed value, macro VBA often specifies this numerical value as a variable.

Assuming that variable i contains a number,
Range(Cells (1, 1), Cells(i, 1))
Range(Cells (1, 1), Cells(1, i))
Write like this:

Specify multiple rows, multiple columns

If there are multiple lines, if 1 to 5 lines,
Range(Rows(1), Rows(5))

In the case of multiple columns, if 1 column (column A) to 3 columns (column C),
Range(Columns(1), Columns(3))
Specify like this.

* While the above is written with a fixed value, macro VBA often specifies this numerical value as a variable.

Assuming that variable i contains a number,
Range(Rows (1), Rows(i))
Range(Columns (1), Columns(i))
Write like this.

Summary of how to use Range and Cells

For how to use Range and Cells properly,
Use Range for cell ranges and name definitions, otherwise use Cells.
This is the basis.

In other words,
When using variables, use Cells, Rows, Columns.

Articles related to the basics of Range and Cells

Range and Cells are the basics of VBA basics, and VBA will not start if this is not understood.
I will explain many things related to Range and Cells in the future.

8.What is putting a character in a cell (Range, Value)
Let's take a closer look at the macro that we ran last time. Why is the value in the cell now How to read VBA I will explain in a little more detail. Anyway now I can see that _Good morning!_ If you want to put characters in a cell Range(_cell address_) = _letters_ The cell address is the column sy…
9.How to specify cells in Range
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.
10.Specification method other than Range (Cells, Rows, Columns)
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_.

31.Cell format (display format, NumberFormatLocal)
By specifying the display format of the cell you can make various appearances even if the value in the cell is the same value. You can keep the cell value as it is and make it look just like the user. Even with the same number 12345 12345 12345 $12
32.Cell format (Alignment)
Macro VBA can be used to specify the position (vertical position, horizontal position) for displaying the value in the cell. It is the content specified by _Cell Formatting_ → _Placement_. Specifying placement in macro VBA Range.
33.Cell format (font, Font)
This is the macro VBA specification method for the typeface of the character displayed in the cell. The font of the cell (Range object) is the Font property. The Font property returns a Font object. I think it's hard to understand.
34.Cell format (fill, interior)
The most effective way to make a cell stand out would be to fill it with a color This is a macro VBA explanation when filling a cell. The fill (pattern) of the cell (Range object) becomes the Interior property. The Interior property returns an Interior object.
35.Cell format (ruled line, border)
Sometimes it is necessary to draw a ruled line with macro VBA. If the data range of the sheet is changed by a macro it will be necessary to redraw the ruled lines. When searching the net etc. the macro that draws ruled lines seems to be written in various ways.

39.Clear the cell (Clear)
How to write macro VBA to clear cells, Even if it is cleared, the VBA code differs depending on what is to be cleared (value, color, comment, etc.). Specifically, the properties used depend on what is cleared in the cell (value, color, comment, etc.
40.Copy, cut and paste of cells (Copy, Cut, Paste)
In macro VBA it becomes the explanation of macro VBA when copying or cutting a cell and pasting it to another cell. By hand Copy (Ctrl + C) or cut (Ctrl + X) a cell and paste it into another cell (ESC or Enter after Ctrl + V) This is a macro VBA that performs the same operation.
41.Copy cells and paste values (PasteSpecial)
Although it was titled Paste Value there are various ways to specify not only the value but also “Paste by selecting format”. This is the macro VBA code for copying a cell and “paste specially” in other cells. Copying a cell value or format to another cell is a standard and essential technique in M…
42.What is copying a cell?
What does it mean to copy a cell ... You can write more practical macro VBA code by digging into the macro VBA of copying cells a bit. What does copy mean in a word? What is copying a cell? A cell is a Range object The object itself cannot be copied.

82.Range Resize property
83.Range Offset property
84.Range Address property
85.Handling merged cells

Related articles on the application of Range and Cells

Read about Range and Cells
reference
Range property list ・・・ Link to detailed explanation page
List of Range methods ・・・ Link to detailed explanation page


Depending on how you write Range and Cells, the look of macro VBA will change significantly.
If the results are the same, try to write readable VBA code.




Articles with the same theme "VBA basics"

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)
18. Get last row (End, Rows.Count)



  • >
  • >
  • 11. How to use Range and Cells

  • 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