VBA for Borders

[steve case]

I have code to paste in a new line of data which I then want to put borders around. The macro recorder results in 25 lines of code to accomplish what appears to be a simple task. Is there a shorter way to say it in VBA?

Here's the code from the recorder:

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

 

[onlyadrafter]

Hello,

Its amazing what removing one word can do

Sub Macro1()
    With Selection.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub

Hows that?

 

[steve case]

Thank you very much

This site is totally Awesome!

 

[Greg Truby]

a. I learned something here - I would not have even tried what Drafter did because I would have guessed that Excel would also have set the Diagonal borders with that syntax.

b. I noticed that in your example you didn't set the Inside Horizontal. If, indeed, you don't want to set Inside Horizontal, then something like the following would work.

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> Test()
    <SPAN style="color:#00007F">Dim</SPAN> myBorders() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, item <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    myBorders = Array(xlEdgeLeft, _
                      xlEdgeTop, _
                      xlEdgeBottom, _
                      xlEdgeRight, _
                      xlInsideVertical)
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> item <SPAN style="color:#00007F">In</SPAN> myBorders
        <SPAN style="color:#00007F">With</SPAN> Selection.Borders(item)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> item
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

You could also do Drafters and then undo the horizontals, but the code above is sort of a generic bit of code that you could easily adapt to various situations in the future.

Regards,

 

[steve case]

Thanks Greg

I will file the Generic one away for just in case. Obviously Drafter's short code is the one I will use.

This site ia my source. I have two of Walkenbach's books, but this sort of thing is not spelled out in either one. Indeed, in Power Programming with VBA, "Borders" isn't even in the index.

I am amazed that I get answers to these types of questions in minutes!

Thanks to both of you.