View Full Version : Solved: Bold last row of spreadsheet
Klartigue
09-12-2011, 01:24 PM
[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
09-12-2011, 01:33 PM
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
09-12-2011, 01:34 PM
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
09-12-2011, 01:38 PM
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
09-12-2011, 02:01 PM
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
09-12-2011, 02:02 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.