Solved: Bold last row of spreadsheet

[Klartigue]

[VBA]Sub BoldLastRow()
'Bold last row
Dim Lastrow As Long

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row.Select
Selection.Font.Bold = True

End Sub[VBA]

I am trying to bold the last row of my spreadsheet..i am missing something??

[Klartigue]

Sub OpenSunrise()
Workbooks.Open Filename:= _
"U:\Axys3\CSV\sunrise.csv"
End Sub
Sub FormatHeader()
'
' Format Header
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Rows("1:3").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
Rows("1:4").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("A:A").ColumnWidth = 9.71
Rows("5:5").Select
Selection.Font.Bold = True
End Sub
Sub OpenSunriseInput()
Workbooks.Open Filename:= _
"G:\Fixed Income\Sunrise Monthly Report\SUNRISE\8.31.2011\Sunrise Perform Input 8.31.2011.xls"
End Sub
Sub Maturity()
' Lookup Maturity

Windows("sunrise.csv").Activate
Application.Run "BLPLinkReset"

Range("AB7").Resize(Range("A7").End(xlDown).Row - 2).FormulaR1C1 = _
"=VLOOKUP(RC[-27],'[Sunrise Perform Input 8.31.2011.xls]Sheet1'!R1:R65536,10,FALSE)"


End Sub
Sub FormatDate()
' Format Date Column
Columns("AB:AB").Select
Selection.NumberFormat = "m/d/yyyy"

End Sub
Sub Duration()
' Lookup Duration
Windows("sunrise.csv").Activate
Application.Run "BLPLinkReset"

Range("AC7").Resize(Range("A7").End(xlDown).Row - 2).FormulaR1C1 = _
"=VLOOKUP(RC[-28],'[Sunrise Perform Input 8.31.2011.xls]Sheet1'!R1:R65536,11,FALSE)"

End Sub
Sub BookYld()
' Lookup Book Yld
Windows("sunrise.csv").Activate
Application.Run "BLPLinkReset"

Range("AD7").Resize(Range("A7").End(xlDown).Row - 2).FormulaR1C1 = _
"=VLOOKUP(RC[-29],'[Sunrise Perform Input 8.31.2011.xls]Sheet1'!R1:R65536,15,FALSE)"

End Sub
Sub MarketYld()
' Lookup Market Yld
Windows("sunrise.csv").Activate
Application.Run "BLPLinkReset"

Range("AE7").Resize(Range("A7").End(xlDown).Row - 2).FormulaR1C1 = _
"=VLOOKUP(RC[-30],'[Sunrise Perform Input 8.31.2011.xls]Sheet1'!R1:R65536,14,FALSE)"

End Sub
Sub TEBookYld()
' T/E BookYld
Range("AF7").Resize(Range("A7").End(xlDown).Row - 2).FormulaR1C1 = _
"=RC[-2]/0.65"

End Sub

Sub TEMarketYld()
' T/E MarketYld
Range("AG7").Resize(Range("A7").End(xlDown).Row - 2).FormulaR1C1 = _
"=RC[-2]/0.65"
ActiveCell.Offset(1, 0).Range("A1").Select

End Sub
Sub FormatColumnK()
' Format Column K
Columns("K").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.NumberFormat = "#,##0.00"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.NumberFormat = "#,##0.0"
Selection.NumberFormat = "#,##0"
End Sub
Sub FormatColumnM()
' Format Column M
Columns("M").Select
Selection.NumberFormat = "#,##0.00;[Red]#,##0.00"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
Sub FormatColumnR()
' Format Column R
Columns("R").Select
Selection.NumberFormat = "#,##0.00;[Red]#,##0.00"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
Sub FormatColumnT()
' Format Column T
Columns("T").Select
Selection.NumberFormat = "#,##0.00;[Red]#,##0.00"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
Sub FormatColumnU()
' Format Column U
Columns("U").Select
Selection.NumberFormat = "#,##0.00;[Red]#,##0.00"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
Sub FormatColumnV()
' Format Column V
Columns("V").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.NumberFormat = "#,##0.00"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.NumberFormat = "#,##0.0"
Selection.NumberFormat = "#,##0"
End Sub
Sub FormatColumnsWtoZ()
' Format Rows W to Z
Columns("W:Z").Select
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "#,##0.00;[Red]#,##0.00"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

Sub MoveCashRow()
'Move cash row
Dim Lastrow As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Rows(6).Copy
.Rows(Lastrow).Insert
End With
End Sub
Sub FormatBorders()
'
' Format cell border

Range("A6:AH6").Resize(Range("A6").End(xlDown).Row - 1).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
Sub SunriseMonthlyReport()
Call OpenSunrise
Call FormatHeader
Call OpenSunriseInput
Call Maturity
Call FormatDate
Call Duration
Call BookYld
Call MarketYld
Call TEBookYld
Call TEMarketYld
Call FormatColumnK
Call FormatColumnM
Call FormatColumnR
Call FormatColumnT
Call FormatColumnU
Call FormatColumnV
Call FormatColumnsWtoZ
Call MoveCashRow
Call FormatBorders


End Sub


My attached spreadsheet is a result of the above macro. As you can see, Sub Maturity, Sub Duration, Sub MarketYld, SubBookYld, Sub TEBookYld, Sub TEMarketYld, all go beyond the last row A64. How do I get all of these to stop at A64?

Thanks for the help

[Bob Phillips]

Lastrow is an integervalue, so you just calcualte that from the row property, you don't also select (none of the code I have given you selects it). You then pass that variable to the Rows object

Sub BoldLastRow()
'Bold last row
Dim Lastrow As Long

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Rows(Lastrow).Font.Bold = True

End Sub


BTW, the tags are vba and /vba within brackets

[Klartigue]

I get a compile error with the following:

Sub BoldLastRow()
'Bold last row
Dim Lastrow As Long

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Rows(Lastrow).Font.Bold = True

End Sub

[Bob Phillips]

That code can be simplified



Sub SunriseMonthlyReport()
Call OpenSunrise
Call FormatHeader
Call OpenSunriseInput
Call LookupValue("AB7", -27) 'Maturity
Columns("AB:AB").NumberFormat = "m/d/yyyy" ' Format Date Column
Call LookupValue("AC7", -28) 'Date
Call LookupValue("AD7", -29) 'BookYld
Call LookupValue("AE7", -30) 'MarketYld
Range("AF7").Resize(Range("A7").End(xlDown).Row - 2).FormulaR1C1 = "=RC[-2]/0.65" 'Calculate TEBookYld
Range("AG7").Resize(Range("A7").End(xlDown).Row - 2).FormulaR1C1 = "=RC[-2]/0.65" 'Calculate TEMarketYld
Call FormatColumn("K", xlLeft, xlBottom, "#,##0")
Call FormatColumn("M", xlLeft, xlBottom, "#,##0.00;[Red]#,##0.00")
Call FormatColumn("R", xlLeft, xlBottom, "#,##0.00;[Red]#,##0.00")
Call FormatColumn("T", xlLeft, xlBottom, "#,##0.00;[Red]#,##0.00")
Call FormatColumn("U", xlLeft, xlBottom, "#,##0.00;[Red]#,##0.00")
Call FormatColumn("V", xlLeft, xlBottom, "#,##0")
Call FormatColumn("W:Z", xlLeft, xlBottom, "#,##0.00;[Red]#,##0.00")
Call MoveCashRow
Call FormatBorders
End Sub

Sub OpenSunrise()
Workbooks.Open Filename:="U:\Axys3\CSV\sunrise.csv"
End Sub

Sub FormatHeader()
' Format Header
Rows("1:2").Delete Shift:=xlUp
With Rows("1:3")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Font.Bold = True
End With
With Rows("1:4").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("A:A").ColumnWidth = 9.71
Rows("5:5").Font.Bold = True
End Sub

Sub OpenSunriseInput()
Workbooks.Open Filename:= _
"G:\Fixed Income\Sunrise Monthly Report\SUNRISE\8.31.2011\Sunrise Perform Input 8.31.2011.xls"
End Sub

Sub LookupValue(ByRef StartCell As String, ByVal LookupOff As Long)
' Setup Lookup formula
Windows("sunrise.csv").Activate
Application.Run "BLPLinkReset"

Range(StartCell).Resize(Range("A7").End(xlDown).Row - 2).FormulaR1C1 = _
"=VLOOKUP(RC[" & LookupOff & "],'[Sunrise Perform Input 8.31.2011.xls]Sheet1'!R1:R65536,11,FALSE)"
End Sub

Sub FormatColumn(ByVal col As String, HAlign As Long, VAlign As Long, NumFormat As String)
' Format Column
With Columns(col)

.HorizontalAlignment = HAlign
.VerticalAlignment = VAlign
.NumberFormat = NumFormat
End With
End Sub

Sub MoveCashRow()
'Move cash row
Dim Lastrow As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Rows(6).Copy
.Rows(Lastrow).Insert
End With
End Sub

Sub FormatBorders()
' Format cell border

With Range("A6:AH6").Resize(Range("A6").End(xlDown).Row - 1)

.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End With
End Sub

[Bob Phillips]

You had forgotten the sheet



Sub BoldLastRow()
'Bold last row
Dim Lastrow As Long

With Activesheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
. Rows(Lastrow).Font.Bold = True
End With