Solved: I want to make code smaller and more optomized

[Djblois]

I have code that I use multiple times in my program and they all work perfectly but I want to know if there is shorter ways or more optimized ways to do the same thing.

First one: I create a border completely around a cell.

[VBA]With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With[/VBA]

Second One: I use these to add Pivot Tables many times in my program

[VBA]pt.PivotFields("Customer").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)[/VBA]

Is it possible to make either statement shorter?

[Zack Barresse]

Hello,

You can shorten your borders to something like...

[vba] Selection.BorderAround xlContinuous, xlThin, 1
Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Selection.Borders(xlInsideHorizontal).ColorIndex = 1
Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideVertical).ColorIndex = 1[/vba]

Can't help you with the other, it looks fairly short to me.

HTH

[Djblois]

the second one is fairly short but I use it like 100 different times - so if I can shorten it to one line it would save me 100 lines of code.

Thank you

[Zack Barresse]

Just put it all on one line, don't know how much shorter you can make it...

[vba]pt.PivotFields("Customer").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False) [/vba]

[Djblois]

thank you

[XLGibbs]

If the line of code is repeated, can you make it a subroutine, and pass the string "Field" through to the sub..? Seems that would be a lot easier than having 100 identical lines..

[vba]

Sub addfieldstoPivot( strField as string)

pt.PivotFields(strField).Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)


End Sub

[/vba]

When you need to add a field

Just:
[VBA]
addfieldstoPivot "Customers"
[/vba]

For example

[johnske]

For the 1st part, try this variation...
[VBA]Option Explicit

Sub FormatSelection()
With Selection
.BorderAround xlContinuous, xlThin, 1
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
End With
End Sub[/VBA]

[tstom]

See the "Styles Collection" in VBA help for a speedier, more concise way of using the same formats for multiple ranges. In essence, you should create a style once and then apply it to your various ranges as need to.

[Zack Barresse]

Good call Tom!

[Djblois]

The Only problem is it isn't exact. It is always a different field name. that is the problem

[lucas]

Then you should be able to use Pete's suggestion from post #6 . Just call an input box to name the field