[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??
Printable View
[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??
[VBA]Sub OpenSunrise()
Workbooks.Open Filename:= _
"U:\Axys3\CSV\sunrise.csv"
End Sub[/VBA]
[VBA]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[/VBA]
[VBA]Sub OpenSunriseInput()
Workbooks.Open Filename:= _
"G:\Fixed Income\Sunrise Monthly Report\SUNRISE\8.31.2011\Sunrise Perform Input 8.31.2011.xls"
End Sub[/VBA]
[VBA]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[/VBA]
[VBA]Sub FormatDate()
' Format Date Column
Columns("AB:AB").Select
Selection.NumberFormat = "m/d/yyyy"
End Sub[/VBA]
[VBA]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[/VBA]
[VBA]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[/VBA]
[VBA]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[/VBA]
[VBA]Sub TEBookYld()
' T/E BookYld
Range("AF7").Resize(Range("A7").End(xlDown).Row - 2).FormulaR1C1 = _
"=RC[-2]/0.65"
End Sub[/VBA]
[VBA]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[/VBA]
[VBA]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[/VBA]
[VBA]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[/VBA]
[VBA]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[/VBA]
[VBA]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[/VBA]
[VBA]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[/VBA]
[VBA]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[/VBA]
[VBA]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[/VBA]
[VBA]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[/VBA]
[VBA]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[/VBA]
[VBA]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[/VBA]
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
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
[vba]Sub BoldLastRow()
'Bold last row
Dim Lastrow As Long
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Rows(Lastrow).Font.Bold = True
End Sub[/vba]
BTW, the tags are vba and /vba within brackets
I get a compile error with the following:
[VBA]Sub BoldLastRow()
'Bold last row
Dim Lastrow As Long
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Rows(Lastrow).Font.Bold = True
[/VBA]End Sub
That code can be simplified
[vba]
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[/vba]
You had forgotten the sheet
[vba]
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[/vba]