PDA

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?

xld
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?

xld
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!