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.

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

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

pt.PivotFields("Customer").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)

Is it possible to make either statement shorter?

[Zack Barresse]

Hello,

You can shorten your borders to something like...

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

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...

pt.PivotFields("Customer").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)

[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..



Sub addfieldstoPivot( strField as string)

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


End Sub



When you need to add a field

Just:

addfieldstoPivot "Customers"


For example

[johnske]

For the 1st part, try this variation...
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

[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! :yes

[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