Solved: I want to make code smaller and more optomized

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?

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

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

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]

thank you

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

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]

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.

Good call Tom! :yes

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

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