Cleaning Up VBA Code

[SonicBoomGolf]

This code is working for me, but I just wanted to see if the pros could give me any suggestions on if this could be cleaned up and simplified a bit (the border portion of my code in particular). I imagine that there are some areas where processes can be done more efficiently, but not sure where. I took this code straight from the file, and have left my comments included also. Let me know if you have any ideas. Thanks.

Sub Macro5()

Finalrow = Range("A65536").End(xlUp).Row

' Text to Columns
Columns("a:a").Select
Range("a" & Finalrow).Activate
Selection.TextToColumns Destination:=Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)

' Freeze Panes
Range("e2").Select
ActiveWindow.FreezePanes = True

' Inserts New Column in Column A
With Range("a:a").Select
Selection.Insert Shift:=xlToRight
End With

' Inserts New Row In Row 1
With Rows("1:1").Select
Selection.Insert Shift:=xlDown
End With

' Enter "Rank" in A2
Range("A2").Value = "Rank"

' Gives all cells 8 Point Font
Cells.Select
Range("A1").Activate
With Selection.Font
.Name = "Arial"
.Size = 8
End With

' Merge Cells (Multiple Ranges)
[f1:m1, n1:u1, v1:ac1, ad1:ak1, al1:as1, at1:ba1, bb1:bi1, cm1:co1].Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
End With
Selection.Font.Bold = True

' Headers in Row 1
Range("f1").Value = "Ft. Worth"
Range("n1").Value = "Indianapolis"
Range("v1").Value = "Jacksonville"
Range("ad1").Value = "Knoxville"
Range("al1").Value = "Modesto"
Range("at1").Value = "Ontario"
Range("bb1").Value = "Network Summary"

' Headers in Row 2
Range("F2, N2, V2, ad2, al2, at2, bb2").Value = "Cases Shipped"
Range("g2, o2, w2, ae2, am2, au2, bc2").Value = "Cases Received"
Range("h2, p2, x2, af2, an2, av2, bd2").Value = "Total Cases Handled (TCH)"
Range("i2, q2, y2, ag2, ao2, aw2, be2").Value = "WHSE Damage"
Range("j2, r2, z2, ah2, ap2, ax2, bf2").Value = "SHIP Damage"
Range("k2, s2, aa2, ai2, aq2, ay2, bg2").Value = "CARR Damage"
Range("l2, t2, ab2, aj2, ar2, az2, bh2").Value = "Total Damage"
Range("m2, u2, ac2, ak2, as2, ba2, bi2").Value = "Damage as a % of TCH"
Range("bj2").Value = "National Standard Price"
Range("bk2").Value = "Additional Trans/Labor Cost Per Case"
Range("bl2").Value = "Total Cost Per Damaged Case"
Range("bm2").Value = "Total Damage Cost"
Range("bn2").Value = "Ft. Worth"
Range("br2").Value = "Indianapolis"
Range("bv2").Value = "Jacksonville"
Range("bz2").Value = "Knoxville"
Range("cd2").Value = "Modesto"
Range("ch2").Value = "Ontario"
Range("cl2").Value = "Network Summary"
Range("bo2, bs2, bw2, ca2, ce2, ci2, cm2").Value = "WHSE Damage $'s"
Range("bp2, bt2, bx2, cb2, cf2, cj2, cn2").Value = "SHIP Damage $'s"
Range("bq2, bw, by2, cc2, cg2, ck2, co2").Value = "CARR Damage $'s"
Range("cm1").Value = "Network Damage Summary"
With Rows("2:2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With

' Puts borders around all cells
Range(Cells(1, 1), Cells(Finalrow + 1, 61)).Select
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
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

' Formatting of cells F - BI
Range(Cells(1, 6), Cells(Finalrow + 1, 13)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = 2
End With

Range(Cells(1, 14), Cells(Finalrow + 1, 21)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = 2
End With

Range(Cells(1, 22), Cells(Finalrow + 1, 29)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = 2
End With

Range(Cells(1, 30), Cells(Finalrow + 1, 37)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = 2
End With

Range(Cells(1, 38), Cells(Finalrow + 1, 45)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
.PatternColorIndex = 2
End With

Range(Cells(1, 46), Cells(Finalrow + 1, 53)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = 2
End With

Range(Cells(1, 54), Cells(Finalrow + 1, 61)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlThick
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = 2
End With

' All the formulas in columns F through I
Range("bj3:bj" & Finalrow).Formula = "=IF(ISNA(VLOOKUP(B3,SAP_NSC_Upload!$A$1:$B$6209,2,FALSE)),0,VLOOKUP(B3,SAP_NSC_Upload!$A$1:$B$6209,2,FALSE))"
Range("bk3:bk" & Finalrow).Formula = ".85"
Range("bl3:bl" & Finalrow).Formula = "=BK3+BJ3"
Range("bm3:bm" & Finalrow).Formula = "=BL3*BH3"
Range(Cells(3, 62), Cells(Finalrow + 1, 93)).NumberFormat = "$#,##0.00"
Range(Cells(1, 65), Cells(Finalrow, 65)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

' Formats Row 2 W/ Bold and Underline
Rows("1:2").Font.Bold = True
With Rows("1:2").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

' Formats the numbers with commas
Range(Cells(3, 6), Cells(Finalrow + 1, 61)).NumberFormat = "#,##0"

' Adjust Column Width in Columns A-E
Columns("E:E").EntireColumn.AutoFit
Columns("D:D").ColumnWidth = 4.29
Columns("C:C").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").ColumnWidth = 5.29

' Adjust Column Width in Columns F-BI
Columns("F:BI").Select
Range("BI2").Activate
Selection.ColumnWidth = 8#

' Adjust Column Width in Columns BJ-BM
Columns("BJ:CO").Select
Range("BJ2").Activate
Selection.ColumnWidth = 10.29

' Fills Cells W/Black
Range("A1:e1").Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
Range(Cells(Finalrow + 1, 3), Cells(Finalrow + 1, 5)).Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With

' Damage as a % of TCH Formulas
Range("m3:m" & Finalrow + 1).Formula = "=IF(H3>0,L3/H3,""N/A"")"
Range("u3:u" & Finalrow + 1).Formula = "=IF(P3>0,T3/P3,""N/A"")"
Range("ac3:ac" & Finalrow + 1).Formula = "=IF(X3>0,AB3/X3,""N/A"")"
Range("ak3:ak" & Finalrow + 1).Formula = "=IF(AF3>0,AJ3/AF3,""N/A"")"
Range("as3:as" & Finalrow + 1).Formula = "=IF(An3>0,Ar3/An3,""N/A"")"
Range("ba3:ba" & Finalrow + 1).Formula = "=IF(AV3>0,AZ3/AV3,""N/A"")"
Range("bi3:bi" & Finalrow + 1).Formula = "=IF(BD3>0,BH3/BD3,""N/A"")"

' Sums up the dollars of damage
Range("bm" & Finalrow + 1).Formula = "=sum(bm3:bm" & Finalrow & ")"

' Thick Line for Bottom Row
Range(Cells(Finalrow + 1, 1), Cells(Finalrow + 1, 71)).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Bold = True
End With

' Formulas to Calculate $'s of Damages by Location
Range("bn3:bn" & Finalrow + 1).Formula = "=BL3*l3"
Range("bo3:bo" & Finalrow + 1).Formula = "=BL3*i3"
Range("bp3:bp" & Finalrow + 1).Formula = "=BL3*j3"
Range("bq3:bq" & Finalrow + 1).Formula = "=BL3*k3"
Range("br3:br" & Finalrow + 1).Formula = "=BL3*t3"
Range("bs3:bs" & Finalrow + 1).Formula = "=BL3*q3"
Range("bt3:bt" & Finalrow + 1).Formula = "=BL3*r3"
Range("bu3:bu" & Finalrow + 1).Formula = "=BL3*s3"
Range("bv3:bv" & Finalrow + 1).Formula = "=BL3*ab3"
Range("bw3:bw" & Finalrow + 1).Formula = "=BL3*y3"
Range("bx3:bx" & Finalrow + 1).Formula = "=BL3*z3"
Range("by3:by" & Finalrow + 1).Formula = "=BL3*aa3"
Range("bz3:bz" & Finalrow + 1).Formula = "=BL3*aj3"
Range("ca3:ca" & Finalrow + 1).Formula = "=BL3*ag3"
Range("cb3:cb" & Finalrow + 1).Formula = "=BL3*ah3"
Range("cc3:cc" & Finalrow + 1).Formula = "=BL3*ai3"
Range("cd3:cd" & Finalrow + 1).Formula = "=BL3*ar3"
Range("ce3:ce" & Finalrow + 1).Formula = "=BL3*ao3"
Range("cf3:cf" & Finalrow + 1).Formula = "=BL3*ap3"
Range("cg3:cg" & Finalrow + 1).Formula = "=BL3*aq3"
Range("ch3:ch" & Finalrow + 1).Formula = "=BL3*az3"
Range("ci3:ci" & Finalrow + 1).Formula = "=BL3*aw3"
Range("cj3:cj" & Finalrow + 1).Formula = "=BL3*ax3"
Range("ck3:ck" & Finalrow + 1).Formula = "=BL3*ay3"
Range("cl3:cl" & Finalrow + 1).Formula = "=BL3*bh3"
Range("cm3:cm" & Finalrow + 1).Formula = "=BL3*be3"
Range("cn3:cn" & Finalrow + 1).Formula = "=BL3*bf3"
Range("co3:co" & Finalrow + 1).Formula = "=BL3*bg3"

' Sums up the Dollars of Damage by Location
Range("bn" & Finalrow + 1).Formula = "=sum(bn3:bn" & Finalrow & ")"
Range("bo" & Finalrow + 1).Formula = "=sum(bo3:bo" & Finalrow & ")"
Range("bp" & Finalrow + 1).Formula = "=sum(bp3:bp" & Finalrow & ")"
Range("bq" & Finalrow + 1).Formula = "=sum(bq3:bq" & Finalrow & ")"
Range("br" & Finalrow + 1).Formula = "=sum(br3:br" & Finalrow & ")"
Range("bs" & Finalrow + 1).Formula = "=sum(bs3:bs" & Finalrow & ")"
Range("bt" & Finalrow + 1).Formula = "=sum(bt3:bt" & Finalrow & ")"
Range("bu" & Finalrow + 1).Formula = "=sum(bu3:bu" & Finalrow & ")"
Range("bv" & Finalrow + 1).Formula = "=sum(bv3:bv" & Finalrow & ")"
Range("bw" & Finalrow + 1).Formula = "=sum(bw3:bw" & Finalrow & ")"
Range("bx" & Finalrow + 1).Formula = "=sum(bx3:bx" & Finalrow & ")"
Range("by" & Finalrow + 1).Formula = "=sum(by3:by" & Finalrow & ")"
Range("bz" & Finalrow + 1).Formula = "=sum(bz3:bz" & Finalrow & ")"
Range("ca" & Finalrow + 1).Formula = "=sum(ca3:ca" & Finalrow & ")"
Range("cb" & Finalrow + 1).Formula = "=sum(cb3:cb" & Finalrow & ")"
Range("cc" & Finalrow + 1).Formula = "=sum(cc3:cc" & Finalrow & ")"
Range("cd" & Finalrow + 1).Formula = "=sum(cd3:cd" & Finalrow & ")"
Range("ce" & Finalrow + 1).Formula = "=sum(ce3:ce" & Finalrow & ")"
Range("cf" & Finalrow + 1).Formula = "=sum(cf3:cf" & Finalrow & ")"
Range("cg" & Finalrow + 1).Formula = "=sum(cg3:cg" & Finalrow & ")"
Range("ch" & Finalrow + 1).Formula = "=sum(ch3:ch" & Finalrow & ")"
Range("ci" & Finalrow + 1).Formula = "=sum(ci3:ci" & Finalrow & ")"
Range("cj" & Finalrow + 1).Formula = "=sum(cj3:cj" & Finalrow & ")"
Range("ck" & Finalrow + 1).Formula = "=sum(ck3:ck" & Finalrow & ")"
Range("cl" & Finalrow + 1).Formula = "=sum(cl3:cl" & Finalrow & ")"
Range("cm" & Finalrow + 1).Formula = "=sum(cm3:cm" & Finalrow & ")"
Range("cn" & Finalrow + 1).Formula = "=sum(cn3:cn" & Finalrow & ")"
Range("co" & Finalrow + 1).Formula = "=sum(co3:co" & Finalrow & ")"

' Color Location Rows Again
Range(Cells(1, 66), Cells(Finalrow + 1, 69)).Interior.ColorIndex = 6
Range(Cells(1, 70), Cells(Finalrow + 1, 73)).Interior.ColorIndex = 40
Range(Cells(1, 74), Cells(Finalrow + 1, 77)).Interior.ColorIndex = 36
Range(Cells(1, 78), Cells(Finalrow + 1, 81)).Interior.ColorIndex = 35
Range(Cells(1, 82), Cells(Finalrow + 1, 85)).Interior.ColorIndex = 8
Range(Cells(1, 86), Cells(Finalrow + 1, 89)).Interior.ColorIndex = 38

' Formatting of Cells as $'s
Range(Cells(2, 66), Cells(Finalrow + 1, 71)).NumberFormat = "$#,##0.00"

End Sub

 

[Tazguy37]

Just a couple quick comments. Any time you use the .Activate (or .Select) method on a range, and then right after that, use Selection.(almost anything), you can shorten it by deleting both the .Select method and the Select on the next line. For example:

    Range("B47:C50").Select
    Selection.Font.Bold = True

becomes:

    Range("B47:C50").Font.Bold = True

Also, I'd look into using R1C1 formulas for grouping like this:

' Damage as a % of TCH Formulas 
Range("m3:m" & Finalrow + 1).Formula = "=IF(H3>0,L3/H3,""N/A"")" 
Range("u3:u" & Finalrow + 1).Formula = "=IF(P3>0,T3/P3,""N/A"")" 
Range("ac3:ac" & Finalrow + 1).Formula = "=IF(X3>0,AB3/X3,""N/A"")" 
...

You might be able to use one R1C1 formula in place of all these. Hope that helps some!

 

[SonicBoomGolf]

I have that VBA and Macros for Microsoft Excel book that is offered on this site, and I remember seeing something in there on both of these suggestions you make. I'll check it out and see what I can do. Thanks!!

 

[Tazguy37]

You're welcome. I didn't look too closely at how your ranges and formulas are distributed. However, you might want to look at reorganizing your sheet, so that your formulas can be copies from the rows or columns next to them. That way, you can use R1C1 references for formulas, and it'll make your code much much shorter and easier to read.

 

[SonicBoomGolf]

Going off you suggesting before that I eliminate the .select portion of code, how would I change the following statement, since there is a "with" statement?

Range(Cells(1, 38), Cells(finalrow + 1, 45)).Select
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
.PatternColorIndex = 2
End With

 

[Tazguy37]

Going off you suggesting before that I eliminate the .select portion of code, how would I change the following statement, since there is a "with" statement?

Range(Cells(1, 38), Cells(finalrow + 1, 45)).Select
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
.PatternColorIndex = 2
End With

Well, "Selection" refers to Range(Cells(1, 38), Cells(finalrow + 1, 45)) in this case, right? So eliminate the line where you select it, and replace Selection with that Range property in your With statement. Like so:

    With Range(Cells(1, 38), Cells(finalrow + 1, 45)).Interior
        .ColorIndex = 8
        .Pattern = xlSolid
        .PatternColorIndex = 2
    End With

Not much of a difference, unless you're doing it LOTS of times, but you get the idea. And if you're doing something like this for thousands of rows sometime, that little difference adds up. Hope that clears it up!