With End With Statement (Excel VBA)

Here is a statement that will save you alot of effort and time when coding your macros. With  End With Statement lets you work on several properties of an object by selecting it only once. This will make your codes more readible while making your coding work easier 

Lets have an example to make it short and sweet:

If you remember our Macro Recorder tutorial, we recorded a macro that changes formatting of the cell we select. Here is a part of the code generated by excel during the recording of this macro:

Sub Macro2()

…more code here…

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

…more code here…

End Sub

You can see that, by the use of With End With statement, left border property of our selected  cell is selected once, than its LineStyle, ColorIndex,TintAndShade and Weight properties are altered in one go.

Otherwise you would have to specify the object infront of the property everytime.