View Full Version : Solved: Apply code to second to last line of spreadsheet
Klartigue
09-13-2011, 08:51 AM
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
How do I get this code to apply to the second to last line in my spreadsheet?
Bob Phillips
09-13-2011, 09:00 AM
We have shown you a dozen times how to calculate the last row. Do that, and subtract 1.
Klartigue
09-13-2011, 10:08 AM
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:
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
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
I would appreciate any help?
Bob Phillips
09-13-2011, 11:01 AM
It exceeds it because your code is wrong.
You start with a bit of code that calculates the lastrow
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
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
Range("A6:AH6").Resize(Range("A6").End(xlDown).Row).Select
This part
Range("A6").End(xlDown).Row)
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
Range("A6").End(xlDown).Row - 5
or in full
Range("A6:AH6").Resize(Range("A6").End(xlDown).Row - 5).Select
although of course there is absolutely no need to select anything, again as we have showed you.
Klartigue
09-13-2011, 12:04 PM
thanks!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.