"With" multiple objects

[Glove303]

Is there a way of specifying "With" for a number of objects.

In other words, could this be coded more efficiently?

With Selection.Borders(xlEdgeBottom)
With Selection.Borders(xlEdgeLeft)
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 33
.TintAndShade = 0
.Weight = xlThick
End With
End With
End With

 

[MrKowz]

Try:

Selection.BorderAround LineStyle:=xlContinuous, Weight:=xlThick, ColorIndex:=33

However, the fact that you have select/selection statements indicates that the whole code might be able to be cleaned up. Can you please post your whole code, and if it isn't enormously large, I'll see what I can do to clean it up and optimize it.

 

[Glove303]

Sorry, I missed the first line off that. The code is:

AccAn.Range(AccAn.Cells(6, Y), AccAn.Cells(LastRowA, Y + 5)).Select
With Selection.Borders(xlEdgeBottom)
With Selection.Borders(xlEdgeLeft)
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 33
.TintAndShade = 0
.Weight = xlThick
End With
End With
End With

I don't think there's any benefit showing the code before this as it's unrelated. It is to do with populating the cells, whereas this is adding a border around them (obviously).

I don't want a border on the top, just the sides and bottom of the range.

Many thanks.

 

[MrKowz]

The benefit to showing unrelated code is that if there are select/selection statements, the speed and effeciency of the code can be greatly improved.

However, to answer your immediate question, try:

With AccAn.Range(AccAn.Cells(6, y), AccAn.Cells(LastRowA, y + 5))
    .BorderAround LineStyle:=xlContinuous, ColorIndex:=33, Weight:=xlThick
    .Borders(xlEdgeTop).LineStyle = xlNone
End With

It will apply a border around the cells, then remove the top border. Otherwise, you need to have a seperate With block for each edge you want to add.

 

[Glove303]

That is great, thank you very much

 

[Glove303]

Out of interest, how do you know when to use := and when just =

?

So say I wanted to add a hairline border on the top, with a color index 0, how would that be best added.



With AccAn.Range(AccAn.Cells(6, y), AccAn.Cells(LastRowA, y + 5)) .BorderAround LineStyle:=xlContinuous, ColorIndex:=33, Weight:=xlThick .Borders(xlEdgeTop).LineStyle:=xlContinuous, ColorIndex:=0, Weight:=xlHairline End With</pre>
This doesn't work for me.

 

[MrKowz]

:= is used when defining multiple parameters of a function without having the need to follow the order of parameters. Not all methods support this. The Borders method (.Borders(xlEdgeTop) for example) does not.

For example, the PasteSpecial method has the following syntax:

expression.PasteSpecial(Paste,Operation,SkipBlanks,Transpose)

If we want to paste just the values, and also transpose the values, we can either use

expression.PasteSpecial(xlPasteValues,,,True)

or

expression.PasteSpecial Paste:=xlPasteValues, Transpose:=True

The second way is much easier to understand, especially when going back and reviewing code.

= is used when defining the value of a variable, object, or application setting.

For your hairline top border, you'll need to refer to it like:

With AccAn.Range(AccAn.Cells(6, y), AccAn.Cells(LastRowA, y + 5))
    .BorderAround LineStyle:=xlContinuous, ColorIndex:=33, Weight:=xlThick
    With .Borders(xlEdgeTop)
        .LineStyle = xlNone
        .ColorIndex = 33
        .Weight = xlHairline
    End With
End With

 

[Glove303]

Out of interest, how do you know when to use := and when just =

?

So say I wanted to add a hairline border on the top, with a color index 0, how would that be best added.



With AccAn.Range(AccAn.Cells(6, y), AccAn.Cells(LastRowA, y + 5))
.BorderAround LineStyle:=xlContinuous, ColorIndex:=33, Weight:=xlThick
.Borders(xlEdgeTop).LineStyle:=xlContinuous, ColorIndex:=0, Weight:=xlHairline End With
This doesn't work for me.

 

[Glove303]

Sorry, made a mess here. I couldnt edit that post as time had expired, but I reposted just as you replied.

Thanks again for the help!

 

[MrKowz]

Sorry, made a mess here. I couldnt edit that post as time had expired, but I reposted just as you replied.

Thanks again for the help!

Just edited my post to include an explanation on that.