Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a macro for inserting a border line every 20 rows
I need a macro for inserting a border line every 20 rows. i have tried to
record this, but I went wrong. Very wrong. It's a bottom border line. (I posted this before, but now I cannot find it). Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a macro for inserting a border line every 20 rows
Try this:
Sub InsertBorderline() StartLine = 20 EndLine = 100 For r = StartLine To EndLine Step 20 With Rows(r).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Next End Sub Regards, Per "Adamp" skrev i meddelelsen ... I need a macro for inserting a border line every 20 rows. i have tried to record this, but I went wrong. Very wrong. It's a bottom border line. (I posted this before, but now I cannot find it). Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a macro for inserting a border line every 20 rows
I'm not sure what is the first row to get the border or what columns should get
the border, but this may give you a start: Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim EveryXRows As Long Dim RngToGetBorder As Range Set wks = Worksheets("sheet1") EveryXRows = 20 With wks Set RngToGetBorder = .Range("A:E") FirstRow = 20 With .UsedRange LastRow = .Cells(.Cells.Count).Row End With For iRow = FirstRow To LastRow Step EveryXRows With Intersect(.Rows(iRow), RngToGetBorder) With .Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End With Next iRow End With End Sub And if you're looking for old posts: You can use google (maybe a few hours behind) to search for stuff you've posted (and find the replies, too) http://groups.google.com/advanced_group_search http://groups.google.com/advanced_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm Adamp wrote: I need a macro for inserting a border line every 20 rows. i have tried to record this, but I went wrong. Very wrong. It's a bottom border line. (I posted this before, but now I cannot find it). Thanks -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a macro for inserting a border line every 20 rows
Cleaned up from recorded macro below
Sub bottombordermod() For i = 1 To 100 Step 20 Rows(i).Borders(xlEdgeBottom).LineStyle = xlContinuous Next i End Sub Sub Macro10() ' ' Macro10 Macro ' Macro recorded 8/20/2008 by Donald B. Guillett ' ' Rows("16:16").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone End Sub Sub bottombordermod() For i = 1 To 50 Step 10 Rows(i).Borders(xlEdgeBottom).LineStyle = xlContinuous Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Adamp" wrote in message ... I need a macro for inserting a border line every 20 rows. i have tried to record this, but I went wrong. Very wrong. It's a bottom border line. (I posted this before, but now I cannot find it). Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a macro for inserting a border line every 20 rows
Is there a way to get the wks variable to change to the active worksheet?
This is quite helpful even if this cannot be done. -- Thanks, Adam P "Dave Peterson" wrote: I'm not sure what is the first row to get the border or what columns should get the border, but this may give you a start: Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim EveryXRows As Long Dim RngToGetBorder As Range Set wks = Worksheets("sheet1") EveryXRows = 20 With wks Set RngToGetBorder = .Range("A:E") FirstRow = 20 With .UsedRange LastRow = .Cells(.Cells.Count).Row End With For iRow = FirstRow To LastRow Step EveryXRows With Intersect(.Rows(iRow), RngToGetBorder) With .Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End With Next iRow End With End Sub And if you're looking for old posts: You can use google (maybe a few hours behind) to search for stuff you've posted (and find the replies, too) http://groups.google.com/advanced_group_search http://groups.google.com/advanced_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm Adamp wrote: I need a macro for inserting a border line every 20 rows. i have tried to record this, but I went wrong. Very wrong. It's a bottom border line. (I posted this before, but now I cannot find it). Thanks -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a macro for inserting a border line every 20 rows
Set wks = Activesheet
Adamp wrote: Is there a way to get the wks variable to change to the active worksheet? This is quite helpful even if this cannot be done. -- Thanks, Adam P "Dave Peterson" wrote: I'm not sure what is the first row to get the border or what columns should get the border, but this may give you a start: Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim EveryXRows As Long Dim RngToGetBorder As Range Set wks = Worksheets("sheet1") EveryXRows = 20 With wks Set RngToGetBorder = .Range("A:E") FirstRow = 20 With .UsedRange LastRow = .Cells(.Cells.Count).Row End With For iRow = FirstRow To LastRow Step EveryXRows With Intersect(.Rows(iRow), RngToGetBorder) With .Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End With Next iRow End With End Sub And if you're looking for old posts: You can use google (maybe a few hours behind) to search for stuff you've posted (and find the replies, too) http://groups.google.com/advanced_group_search http://groups.google.com/advanced_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm Adamp wrote: I need a macro for inserting a border line every 20 rows. i have tried to record this, but I went wrong. Very wrong. It's a bottom border line. (I posted this before, but now I cannot find it). Thanks -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a macro for inserting a border line every 20 rows
simply change
Set wks = Worksheets("sheet1") to Set wks = activesheet "Adamp" wrote: Is there a way to get the wks variable to change to the active worksheet? This is quite helpful even if this cannot be done. -- Thanks, Adam P "Dave Peterson" wrote: I'm not sure what is the first row to get the border or what columns should get the border, but this may give you a start: Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim EveryXRows As Long Dim RngToGetBorder As Range Set wks = Worksheets("sheet1") EveryXRows = 20 With wks Set RngToGetBorder = .Range("A:E") FirstRow = 20 With .UsedRange LastRow = .Cells(.Cells.Count).Row End With For iRow = FirstRow To LastRow Step EveryXRows With Intersect(.Rows(iRow), RngToGetBorder) With .Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End With Next iRow End With End Sub And if you're looking for old posts: You can use google (maybe a few hours behind) to search for stuff you've posted (and find the replies, too) http://groups.google.com/advanced_group_search http://groups.google.com/advanced_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm Adamp wrote: I need a macro for inserting a border line every 20 rows. i have tried to record this, but I went wrong. Very wrong. It's a bottom border line. (I posted this before, but now I cannot find it). Thanks -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a macro for inserting a border line every 20 rows
That was too easy. Thanks!! Works like a charm. All I did was change it
from column E to Z. -- Thanks, Adam P "Dave Peterson" wrote: Set wks = Activesheet Adamp wrote: Is there a way to get the wks variable to change to the active worksheet? This is quite helpful even if this cannot be done. -- Thanks, Adam P "Dave Peterson" wrote: I'm not sure what is the first row to get the border or what columns should get the border, but this may give you a start: Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim EveryXRows As Long Dim RngToGetBorder As Range Set wks = Worksheets("sheet1") EveryXRows = 20 With wks Set RngToGetBorder = .Range("A:E") FirstRow = 20 With .UsedRange LastRow = .Cells(.Cells.Count).Row End With For iRow = FirstRow To LastRow Step EveryXRows With Intersect(.Rows(iRow), RngToGetBorder) With .Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End With Next iRow End With End Sub And if you're looking for old posts: You can use google (maybe a few hours behind) to search for stuff you've posted (and find the replies, too) http://groups.google.com/advanced_group_search http://groups.google.com/advanced_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm Adamp wrote: I need a macro for inserting a border line every 20 rows. i have tried to record this, but I went wrong. Very wrong. It's a bottom border line. (I posted this before, but now I cannot find it). Thanks -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need a macro for inserting a border line every 20 rows
Gee, I guess my offering was just sooooooooo short.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Adamp" wrote in message ... That was too easy. Thanks!! Works like a charm. All I did was change it from column E to Z. -- Thanks, Adam P "Dave Peterson" wrote: Set wks = Activesheet Adamp wrote: Is there a way to get the wks variable to change to the active worksheet? This is quite helpful even if this cannot be done. -- Thanks, Adam P "Dave Peterson" wrote: I'm not sure what is the first row to get the border or what columns should get the border, but this may give you a start: Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim EveryXRows As Long Dim RngToGetBorder As Range Set wks = Worksheets("sheet1") EveryXRows = 20 With wks Set RngToGetBorder = .Range("A:E") FirstRow = 20 With .UsedRange LastRow = .Cells(.Cells.Count).Row End With For iRow = FirstRow To LastRow Step EveryXRows With Intersect(.Rows(iRow), RngToGetBorder) With .Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End With Next iRow End With End Sub And if you're looking for old posts: You can use google (maybe a few hours behind) to search for stuff you've posted (and find the replies, too) http://groups.google.com/advanced_group_search http://groups.google.com/advanced_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm Adamp wrote: I need a macro for inserting a border line every 20 rows. i have tried to record this, but I went wrong. Very wrong. It's a bottom border line. (I posted this before, but now I cannot find it). Thanks -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help- Inserting Blank Rows | Excel Discussion (Misc queries) | |||
Macro needed for inserting blank rows | Excel Worksheet Functions | |||
INserting Specific Number of Rows via macro | Links and Linking in Excel | |||
10 000 Line, every 2 Lines need a border and a open line inbet. e. | Excel Discussion (Misc queries) | |||
Inserting Blank Rows Macro? | Excel Worksheet Functions |