|
Adding new rows with borders
Hi. I have made a macro which adds new rows before the last row. It all works fine but I know need the macro to add horizontal dotted lines every third row. this is the current code Code: -------------------- Sub insRows() Dim ib As Integer Dim x As Long, r As Range ib = Application.InputBox("Enter number of rows to be added", "Add Lines", "5", , , "", , "1") If ib < False Then If ib < 1 Then Exit Sub x = Range("A65536").End(xlUp).Row Set r = Range("A" & x).Resize(ib) r.EntireRow.Insert r.Offset(ib).RowHeight = 12.75 Set r2 = Range(Cells(x, "P"), Cells(x + ib - 1, "P")) myformula = "=SUM(E" & x & ":M" & x & ")" r2.Formula = myformula End If End Sub -------------------- I have tried many things but I cannot get just horizontal dotted lines every third row. Thank you in advance. -- Corless ------------------------------------------------------------------------ Corless's Profile: http://www.excelforum.com/member.php...o&userid=26711 View this thread: http://www.excelforum.com/showthread...hreadid=399713 |
Adding new rows with borders
You might be able to adapt the following code, it applies a dotted line
every third row (counting from 1) for the range. Dim rw As Range For Each rw In Range("A1:A100") If rw.Row / 3 - Int(rw.Row / 3) = 0 Then rw.EntireRow.Borders(xlEdgeBottom).LineStyle = xlDot End If Next -- Cheers Nigel "Corless" wrote in message ... Hi. I have made a macro which adds new rows before the last row. It all works fine but I know need the macro to add horizontal dotted lines every third row. this is the current code Code: -------------------- Sub insRows() Dim ib As Integer Dim x As Long, r As Range ib = Application.InputBox("Enter number of rows to be added", "Add Lines", "5", , , "", , "1") If ib < False Then If ib < 1 Then Exit Sub x = Range("A65536").End(xlUp).Row Set r = Range("A" & x).Resize(ib) r.EntireRow.Insert r.Offset(ib).RowHeight = 12.75 Set r2 = Range(Cells(x, "P"), Cells(x + ib - 1, "P")) myformula = "=SUM(E" & x & ":M" & x & ")" r2.Formula = myformula End If End Sub -------------------- I have tried many things but I cannot get just horizontal dotted lines every third row. Thank you in advance. -- Corless ------------------------------------------------------------------------ Corless's Profile: http://www.excelforum.com/member.php...o&userid=26711 View this thread: http://www.excelforum.com/showthread...hreadid=399713 |
Adding new rows with borders
Hi Don,
How would you adapt this to apply the border only across the columns used range as well -still keeping with the every third row scenario? (Assuming I didn't know the last column when the macro was run.) Thanks in advance! Patti "Don Guillett" wrote in message ... or something like this Sub doborders() For I = 3 To cells(rows.count,"a").end(xlup).row Step 3 Rows(I).Borders(xlEdgeBottom).LineStyle = xlDot Next End Sub -- Don Guillett SalesAid Software "Nigel" wrote in message ... You might be able to adapt the following code, it applies a dotted line every third row (counting from 1) for the range. Dim rw As Range For Each rw In Range("A1:A100") If rw.Row / 3 - Int(rw.Row / 3) = 0 Then rw.EntireRow.Borders(xlEdgeBottom).LineStyle = xlDot End If Next -- Cheers Nigel "Corless" wrote in message ... Hi. I have made a macro which adds new rows before the last row. It all works fine but I know need the macro to add horizontal dotted lines every third row. this is the current code Code: -------------------- Sub insRows() Dim ib As Integer Dim x As Long, r As Range ib = Application.InputBox("Enter number of rows to be added", "Add Lines", "5", , , "", , "1") If ib < False Then If ib < 1 Then Exit Sub x = Range("A65536").End(xlUp).Row Set r = Range("A" & x).Resize(ib) r.EntireRow.Insert r.Offset(ib).RowHeight = 12.75 Set r2 = Range(Cells(x, "P"), Cells(x + ib - 1, "P")) myformula = "=SUM(E" & x & ":M" & x & ")" r2.Formula = myformula End If End Sub -------------------- I have tried many things but I cannot get just horizontal dotted lines every third row. Thank you in advance. -- Corless ------------------------------------------------------------------------ Corless's Profile: http://www.excelforum.com/member.php...o&userid=26711 View this thread: http://www.excelforum.com/showthread...hreadid=399713 |
Adding new rows with borders
Sub doborders()
Dim icol as Long Dim i as Long with ActiveSheet.UsedRange icol = .columns(.columns.count).Column End With For I = 3 To cells(rows.count,"a").end(xlup).row Step 3 cells(i,1).Resize(1,icol).Borders(xlEdgeBottom).Li neStyle = xlDot Next End Sub -- Regards, Tom Ogilvy "Patti" wrote in message ... Hi Don, How would you adapt this to apply the border only across the columns used range as well -still keeping with the every third row scenario? (Assuming I didn't know the last column when the macro was run.) Thanks in advance! Patti "Don Guillett" wrote in message ... or something like this Sub doborders() For I = 3 To cells(rows.count,"a").end(xlup).row Step 3 Rows(I).Borders(xlEdgeBottom).LineStyle = xlDot Next End Sub -- Don Guillett SalesAid Software "Nigel" wrote in message ... You might be able to adapt the following code, it applies a dotted line every third row (counting from 1) for the range. Dim rw As Range For Each rw In Range("A1:A100") If rw.Row / 3 - Int(rw.Row / 3) = 0 Then rw.EntireRow.Borders(xlEdgeBottom).LineStyle = xlDot End If Next -- Cheers Nigel "Corless" wrote in message ... Hi. I have made a macro which adds new rows before the last row. It all works fine but I know need the macro to add horizontal dotted lines every third row. this is the current code Code: -------------------- Sub insRows() Dim ib As Integer Dim x As Long, r As Range ib = Application.InputBox("Enter number of rows to be added", "Add Lines", "5", , , "", , "1") If ib < False Then If ib < 1 Then Exit Sub x = Range("A65536").End(xlUp).Row Set r = Range("A" & x).Resize(ib) r.EntireRow.Insert r.Offset(ib).RowHeight = 12.75 Set r2 = Range(Cells(x, "P"), Cells(x + ib - 1, "P")) myformula = "=SUM(E" & x & ":M" & x & ")" r2.Formula = myformula End If End Sub -------------------- I have tried many things but I cannot get just horizontal dotted lines every third row. Thank you in advance. -- Corless ------------------------------------------------------------------------ Corless's Profile: http://www.excelforum.com/member.php...o&userid=26711 View this thread: http://www.excelforum.com/showthread...hreadid=399713 |
Adding new rows with borders
Thanks Tom!
Regards, Patti "Tom Ogilvy" wrote in message ... Sub doborders() Dim icol as Long Dim i as Long with ActiveSheet.UsedRange icol = .columns(.columns.count).Column End With For I = 3 To cells(rows.count,"a").end(xlup).row Step 3 cells(i,1).Resize(1,icol).Borders(xlEdgeBottom).Li neStyle = xlDot Next End Sub -- Regards, Tom Ogilvy "Patti" wrote in message ... Hi Don, How would you adapt this to apply the border only across the columns used range as well -still keeping with the every third row scenario? (Assuming I didn't know the last column when the macro was run.) Thanks in advance! Patti "Don Guillett" wrote in message ... or something like this Sub doborders() For I = 3 To cells(rows.count,"a").end(xlup).row Step 3 Rows(I).Borders(xlEdgeBottom).LineStyle = xlDot Next End Sub -- Don Guillett SalesAid Software "Nigel" wrote in message ... You might be able to adapt the following code, it applies a dotted line every third row (counting from 1) for the range. Dim rw As Range For Each rw In Range("A1:A100") If rw.Row / 3 - Int(rw.Row / 3) = 0 Then rw.EntireRow.Borders(xlEdgeBottom).LineStyle = xlDot End If Next -- Cheers Nigel "Corless" wrote in message ... Hi. I have made a macro which adds new rows before the last row. It all works fine but I know need the macro to add horizontal dotted lines every third row. this is the current code Code: -------------------- Sub insRows() Dim ib As Integer Dim x As Long, r As Range ib = Application.InputBox("Enter number of rows to be added", "Add Lines", "5", , , "", , "1") If ib < False Then If ib < 1 Then Exit Sub x = Range("A65536").End(xlUp).Row Set r = Range("A" & x).Resize(ib) r.EntireRow.Insert r.Offset(ib).RowHeight = 12.75 Set r2 = Range(Cells(x, "P"), Cells(x + ib - 1, "P")) myformula = "=SUM(E" & x & ":M" & x & ")" r2.Formula = myformula End If End Sub -------------------- I have tried many things but I cannot get just horizontal dotted lines every third row. Thank you in advance. -- Corless ------------------------------------------------------------------------ Corless's Profile: http://www.excelforum.com/member.php...o&userid=26711 View this thread: http://www.excelforum.com/showthread...hreadid=399713 |
Adding new rows with borders
Thanks for the help everyone -- RICOU ----------------------------------------------------------------------- RICOUK's Profile: http://www.excelforum.com/member.php...fo&userid=1879 View this thread: http://www.excelforum.com/showthread.php?threadid=39971 |
| All times are GMT +1. The time now is 02:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com