Managing Data with Microsoft® Excel

Using With Blocks

A handy and useful way of using dot notation concerns what are termed With-End With blocks, or simply With blocks. You use the keyword With, teamed with an object of some sort, to indicate that one or more subsequent statements pertain to other objects (or properties or methods) that belong to the object named in the With. Everything from the With statement through the accompanying End With statement is considered part of the With block.

It's a lot easier to understand With blocks by looking at them than by reading about them. Here's an example, first with no With block and then using one:

ActiveSheet.Rows(2).Font.ColorIndex = 3
ActiveSheet.Rows(2).Font.Bold = True

These two statements, when executed, turn the font color of ...