Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting a Border On a Partial Row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting a Border On a Partial Row
Activesheet.Range("G7").End(xldown).Offset(1,0).Re size(1,7).Select
-- Regards, Tom Ogilvy "Normajm" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting a Border On a Partial Row
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).R esize (1,7).Select -- Regards, Tom Ogilvy "Normajm" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
putting a name in the name box | Excel Discussion (Misc queries) | |||
One partial table, one master table - update the partial | Excel Discussion (Misc queries) | |||
border on last cell of page effects border on beginning cell of ne | Excel Discussion (Misc queries) | |||
Find partial match from column A,B and fill partial match in C? | Excel Discussion (Misc queries) | |||
Changing the border of one cell s/n change the border of adjacent | Excel Discussion (Misc queries) |