Putting a Border On a Partial Row

N

Normajm

Here is some code I wrote to put a border at the bottom of a list of numbers, but above the totaling functions and text I don't know how to go to the bottom of column G, then down 1 row, then select the cells from there left across to column M. The number of rows in the table will change from one time to the next, but the number of columns will not. Any help would be appreciated. I've used Excel for a number of years, but haven't had a need to do programming until now.


Private Sub btnSummary_Click()
ActiveSheet.Range("L7").End(xlDown).Offset(1, 0).Formula = "TOTAL"
ActiveSheet.Range("L7").End(xlDown).Offset(0, 0).HorizontalAlignment = xlRight
ActiveSheet.Range("M7").End(xlDown).Offset(1, 0).FormulaR1C1 = "=Sum(R2C:R[-1]C)"
ActiveSheet.Range("L7").End(xlDown).Offset(1, 0).Formula = "Total Times 71.25%"
ActiveSheet.Range("L7").End(xlDown).Offset(0, 0).HorizontalAlignment = xlRight
ActiveSheet.Range("M7").End(xlDown).Offset(1, 0).FormulaR1C1 = "=.7125*(R[-1]C)"
Range("G7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("H7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("I7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("J7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("K7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("L7").End(xlDown).Offset(-1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("M7").End(xlDown).Offset(-1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("G8").Select

End Sub
 
T

Tom Ogilvy

Activesheet.Range("G7").End(xldown).Offset(1,0).Resize(1,7).Select

--
Regards,
Tom Ogilvy

Normajm said:
Here is some code I wrote to put a border at the bottom of a list of
Click to expand...
numbers, but above the totaling functions and text I don't know how to go
to the bottom of column G, then down 1 row, then select the cells from there
left across to column M. The number of rows in the table will change from
one time to the next, but the number of columns will not. Any help would be
appreciated. I've used Excel for a number of years, but haven't had a need
to do programming until now.
Private Sub btnSummary_Click()
ActiveSheet.Range("L7").End(xlDown).Offset(1, 0).Formula = "TOTAL"
ActiveSheet.Range("L7").End(xlDown).Offset(0, 0).HorizontalAlignment = xlRight
ActiveSheet.Range("M7").End(xlDown).Offset(1, 0).FormulaR1C1 = "=Sum(R2C:R[-1]C)"
ActiveSheet.Range("L7").End(xlDown).Offset(1, 0).Formula = "Total Times 71.25%"
ActiveSheet.Range("L7").End(xlDown).Offset(0, 0).HorizontalAlignment = xlRight
ActiveSheet.Range("M7").End(xlDown).Offset(1, 0).FormulaR1C1 = "=.7125*(R[-1]C)"
Range("G7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("H7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("I7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("J7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("K7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("L7").End(xlDown).Offset(-1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("M7").End(xlDown).Offset(-1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("G8").Select

End Sub
Click to expand...
 
N

Normajm

Thank you. I've been learning so much here by reading
other people's questions and the replies to them. Keep up
the good work.
-----Original Message-----
Activesheet.Range("G7").End(xldown).Offset(1,0).Resize (1,7).Select

--
Regards,
Tom Ogilvy

Normajm said:
Here is some code I wrote to put a border at the bottom
Click to expand...
of a list of
numbers, but above the totaling functions and text I don't know how to go
to the bottom of column G, then down 1 row, then select the cells from there
left across to column M. The number of rows in the table will change from
one time to the next, but the number of columns will not. Any help would be
appreciated. I've used Excel for a number of years, but haven't had a need
to do programming until now.
Private Sub btnSummary_Click()
ActiveSheet.Range("L7").End(xlDown).Offset(1, 0).Formula = "TOTAL"
ActiveSheet.Range("L7").End(xlDown).Offset(0,
Click to expand...
0).HorizontalAlignment =
xlRight
ActiveSheet.Range("M7").End(xlDown).Offset(1,
Click to expand...
0).FormulaR1C1 =
"=Sum(R2C:R[-1]C)"
ActiveSheet.Range("L7").End(xlDown).Offset(1,
Click to expand...
0).Formula = "Total Times
71.25%"
ActiveSheet.Range("L7").End(xlDown).Offset(0,
Click to expand...
0).HorizontalAlignment =
xlRight
ActiveSheet.Range("M7").End(xlDown).Offset(1,
Click to expand...
0).FormulaR1C1 =
"=.7125*(R[-1]C)"
Range("G7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("H7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("I7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("J7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("K7").End(xlDown).Offset(1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("L7").End(xlDown).Offset(-1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("M7").End(xlDown).Offset(-1, 0).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("G8").Select

End Sub
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help needed with borders 2
Border formatting row of cells 22
Hlp! Adv Filter - Non-Contiguous rows don't copy 11
Macro to sort, format, sum, a dynamic set of data 2
Help with macro 1
Loops through Sheets but doesn't work. 5
How to rewrite this code? (Second half) 1
Formatting stop when reach blank cell 6

Top