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

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

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

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

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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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

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

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

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

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