Using the With End With structure

Do you need to set a number of properties for an object? Your code runs faster if you use the With-End With structure. An additional benefit is that your code may be easier to read.

The following code does not use With-End With:

Selection.HorizontalAlignment = xlCenter

Selection.VerticalAlignment = Selection.WrapText = True Selection.Orientation = 0 Selection.ShrinkToFit = False Selection.MergeCells = False xlCenter

Here's the same code, rewritten to use With-End With:

With Selection


= xlCenter

.VerticalAlignment =


.WrapText = True

.Orientation = 0

.ShrinkToFit = False

.MergeCells = False

End With

If this structure seems familiar to you, it's probably because the macro recorder uses With-End With whenever it can.

0 0

Post a comment