I need a macro for inserting a border line every 20 rows

[Adamp]

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

 

[Per Jessen]

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

 

[Dave Peterson]

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_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

 

[Don Guillett]

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

 

[Adamp]

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.

 

[Don Guillett]

May want to change to
For i = 20 To 100 Step 20

 

[Dave Peterson]

Set wks = Activesheet

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.

 

[Duke Carey]

simply change

Set wks = Worksheets("sheet1")

to

Set wks = activesheet

 

[Adamp]

That was too easy. Thanks!! Works like a charm. All I did was change it
from column E to Z.

 

[Don Guillett]

Gee, I guess my offering was just sooooooooo short.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

That was too easy. Thanks!! Works like a charm. All I did was change it
from column E to Z.