2. Avoid Referring the Same Object
If you are dealing with same object again and again, avoid calling same object again and again. Instead, you use With … End With Clause. This will be accurate and faster.
For example, if you want to write some data into Range B1 of Sheet2 and formatting it, you may write your code as shown below:
Sheets(“Sheet2”).Range(“B1”).Value = 25
Sheets(“Sheet2”).Range(“B1”).Font.ColorIndex = 3
Sheets(“Sheet2”).Range(“B1”).Font.Bold = True
So, every time it executes the statements. It has to find the worksheet, and Range and then change the properties. If you use With … End With clause, it will hold the object reference and you can change its properties or execute its methods.
The same code, you can write as shown below:
.Value = 25
.Font.ColorIndex = 3
.Font.Bold = True