| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Innsert a row in microsft excel 2007 - rows without borders | Excel Discussion (Misc queries) | |||
| Sort rows and move borders with rows | New Users to Excel | |||
| Hidden rows in worksheet also hide borders | Excel Discussion (Misc queries) | |||
| Adding row without borders | Excel Discussion (Misc queries) | |||
| Adding Borders | Excel Worksheet Functions | |||
Hybrid Mode
