Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Insert Row above Totals
I have been reading the many questions and responses on this but have been
unable to determine the best way to modify the code I have. I have recorded a macro to insert a row with "inside/outside" borders. This is running fine, however I would like the row to be inserted above the last line, as opposed to in the same place every time. I have pasted the code I am currently using below, any advice would be greatly appreciated. Sub AddNewLine() Range("A15").Select Selection.EntireRow.Insert Range("A15:AG15").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Insert Row above Totals
Try adding this to the top and remove the first 3 lines from your current code
Dim last_row as Integer last_row = Range("A65536").End(xlup) Range("A" & last_row).Select Selection.EntireRow.Insert Range("A" & last_row & ":AG" & last_row).Select "D" wrote: I have been reading the many questions and responses on this but have been unable to determine the best way to modify the code I have. I have recorded a macro to insert a row with "inside/outside" borders. This is running fine, however I would like the row to be inserted above the last line, as opposed to in the same place every time. I have pasted the code I am currently using below, any advice would be greatly appreciated. Sub AddNewLine() Range("A15").Select Selection.EntireRow.Insert Range("A15:AG15").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Insert Row above Totals
Thanks, I am not getting a 'type mismatch' error however. It seems to be
having trouble with the line last_row = Range("A65536").End(xlup) "akphidelt" wrote: Try adding this to the top and remove the first 3 lines from your current code Dim last_row as Integer last_row = Range("A65536").End(xlup) Range("A" & last_row).Select Selection.EntireRow.Insert Range("A" & last_row & ":AG" & last_row).Select "D" wrote: I have been reading the many questions and responses on this but have been unable to determine the best way to modify the code I have. I have recorded a macro to insert a row with "inside/outside" borders. This is running fine, however I would like the row to be inserted above the last line, as opposed to in the same place every time. I have pasted the code I am currently using below, any advice would be greatly appreciated. Sub AddNewLine() Range("A15").Select Selection.EntireRow.Insert Range("A15:AG15").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Insert Row above Totals
Insert the following line after Range("A15").Select
Range("A15").Offset(Selection.Rows.Count - 1, 0).Select and run the macro after selecting all rows in the range If you don't want to maually select then first line in your macro should select all the rows. The above should enable to make it work. Essential part is using the Offset to reach the correct row. "D" wrote: I have been reading the many questions and responses on this but have been unable to determine the best way to modify the code I have. I have recorded a macro to insert a row with "inside/outside" borders. This is running fine, however I would like the row to be inserted above the last line, as opposed to in the same place every time. I have pasted the code I am currently using below, any advice would be greatly appreciated. Sub AddNewLine() Range("A15").Select Selection.EntireRow.Insert Range("A15:AG15").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Insert Row above Totals
How do I tell it to select all rows?
"Sheeloo" wrote: Insert the following line after Range("A15").Select Range("A15").Offset(Selection.Rows.Count - 1, 0).Select and run the macro after selecting all rows in the range If you don't want to maually select then first line in your macro should select all the rows. The above should enable to make it work. Essential part is using the Offset to reach the correct row. "D" wrote: I have been reading the many questions and responses on this but have been unable to determine the best way to modify the code I have. I have recorded a macro to insert a row with "inside/outside" borders. This is running fine, however I would like the row to be inserted above the last line, as opposed to in the same place every time. I have pasted the code I am currently using below, any advice would be greatly appreciated. Sub AddNewLine() Range("A15").Select Selection.EntireRow.Insert Range("A15:AG15").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Insert Row above Totals
Damn forgot to add .row
it should be last_row = Range("A65536").End(xlup).row "D" wrote: Thanks, I am not getting a 'type mismatch' error however. It seems to be having trouble with the line last_row = Range("A65536").End(xlup) "akphidelt" wrote: Try adding this to the top and remove the first 3 lines from your current code Dim last_row as Integer last_row = Range("A65536").End(xlup) Range("A" & last_row).Select Selection.EntireRow.Insert Range("A" & last_row & ":AG" & last_row).Select "D" wrote: I have been reading the many questions and responses on this but have been unable to determine the best way to modify the code I have. I have recorded a macro to insert a row with "inside/outside" borders. This is running fine, however I would like the row to be inserted above the last line, as opposed to in the same place every time. I have pasted the code I am currently using below, any advice would be greatly appreciated. Sub AddNewLine() Range("A15").Select Selection.EntireRow.Insert Range("A15:AG15").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Insert Row above Totals
Pl. use the following code (replace Sheet4 with the name of your sheet)
Sub AddNewLine() Range("A15").Offset(Sheet4.UsedRange.Rows.Count - 1, 0).Select Selection.EntireRow.Insert Sheet4.UsedRange.Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub where sheet1 is the name of the sheet containing your data. "D" wrote: How do I tell it to select all rows? "Sheeloo" wrote: Insert the following line after Range("A15").Select Range("A15").Offset(Selection.Rows.Count - 1, 0).Select and run the macro after selecting all rows in the range If you don't want to maually select then first line in your macro should select all the rows. The above should enable to make it work. Essential part is using the Offset to reach the correct row. "D" wrote: I have been reading the many questions and responses on this but have been unable to determine the best way to modify the code I have. I have recorded a macro to insert a row with "inside/outside" borders. This is running fine, however I would like the row to be inserted above the last line, as opposed to in the same place every time. I have pasted the code I am currently using below, any advice would be greatly appreciated. Sub AddNewLine() Range("A15").Select Selection.EntireRow.Insert Range("A15:AG15").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Totals: Group totals different from Grand totals | Excel Discussion (Misc queries) | |||
make a macro to insert a macro | Excel Discussion (Misc queries) | |||
Copy and Paste Macro for front totals sheet | Excel Discussion (Misc queries) | |||
macro for invoice totals | Excel Discussion (Misc queries) | |||
running totals in a macro for each month | Excel Worksheet Functions |