Solved: Apply code to second to last line of spreadsheet

[Klartigue]

[VBA]Sub FormatRow()
'
' Format Row
Rows("64:64").Select
Selection.RowHeight = 6.75
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 = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.RowHeight = 9
End Sub[/VBA]


How do I get this code to apply to the second to last line in my spreadsheet?

[Bob Phillips]

We have shown you a dozen times how to calculate the last row. Do that, and subtract 1.

[Klartigue]

Sorry, i figured it out. The reason I asked is because as you can see on the attachment, some parts of my macro exceed the last row even though there is a last row function so i am not sure what I am missing?

The following should end at the last row but exceed it:

[vba]Sub FormatBorders()
' Format cell border

Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A6:AH6").Resize(Range("A6").End(xlDown).Row).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 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]

I would appreciate any help?

[Bob Phillips]

It exceeds it because your code is wrong.

You start with a bit of code that calculates the lastrow

[vba]
LastRow = Cells(Rows.Count, "A").End(xlUp).Row[/vba]

but you ignore it. Then you have some code to select from A6 across to AH6 and down to the last row (which is row 62), using

[vba]
Range("A6:AH6").Resize(Range("A6").End(xlDown).Row).Select
[/vba]

This part

[vba]
Range("A6").End(xlDown).Row)[/vba]

calculates the last row okay, 62, but resizing by that number of rows is BAD, because you start at row 6, so resizing 6 by 62 rows gives you 6:67, you need to subtract 5 to account for your start point

[vba] Range("A6").End(xlDown).Row - 5[/vba]

or in full

[vba] Range("A6:AH6").Resize(Range("A6").End(xlDown).Row - 5).Select[/vba]

although of course there is absolutely no need to select anything, again as we have showed you.

[Klartigue]

thanks!