Adding new rows with borders

[Corless]

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

Please Login or Register  to view this content.

I have tried many things but I cannot get just horizontal dotted lines every third row.

Thank you in advance.

[Nigel]

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" <Corless.1ufc2c_1125147919.2281@excelforum-nospam.com> wrote in
message news:Corless.1ufc2c_1125147919.2281@excelforum-nospam.com...
>
> 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
>

[Don Guillett]

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
donaldb@281.com
"Nigel" <nigel-sw@suxnospampanet.com> wrote in message
news:uCor95wqFHA.3352@TK2MSFTNGP14.phx.gbl...
> 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" <Corless.1ufc2c_1125147919.2281@excelforum-nospam.com> wrote in
> message news:Corless.1ufc2c_1125147919.2281@excelforum-nospam.com...
> >
> > 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
> >
>
>

[Patti]

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" <donaldb@281.com> wrote in message
news:%230WIAoxqFHA.240@tk2msftngp13.phx.gbl...
> 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
> donaldb@281.com
> "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
> news:uCor95wqFHA.3352@TK2MSFTNGP14.phx.gbl...
>> 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" <Corless.1ufc2c_1125147919.2281@excelforum-nospam.com> wrote in
>> message news:Corless.1ufc2c_1125147919.2281@excelforum-nospam.com...
>> >
>> > 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
>> >
>>
>>
>
>

[Tom Ogilvy]

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).LineStyle = xlDot
Next
End Sub

--
Regards,
Tom Ogilvy

"Patti" <anonymous@discussions.microsoft.com> wrote in message
news:43108945_2@newspeer2.tds.net...
> 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" <donaldb@281.com> wrote in message
> news:%230WIAoxqFHA.240@tk2msftngp13.phx.gbl...
> > 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
> > donaldb@281.com
> > "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
> > news:uCor95wqFHA.3352@TK2MSFTNGP14.phx.gbl...
> >> 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" <Corless.1ufc2c_1125147919.2281@excelforum-nospam.com> wrote
in
> >> message news:Corless.1ufc2c_1125147919.2281@excelforum-nospam.com...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

[Patti]

Thanks Tom!

Regards,
Patti

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:eSpcIAyqFHA.3080@TK2MSFTNGP15.phx.gbl...
> 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).LineStyle = xlDot
> Next
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Patti" <anonymous@discussions.microsoft.com> wrote in message
> news:43108945_2@newspeer2.tds.net...
>> 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" <donaldb@281.com> wrote in message
>> news:%230WIAoxqFHA.240@tk2msftngp13.phx.gbl...
>> > 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
>> > donaldb@281.com
>> > "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
>> > news:uCor95wqFHA.3352@TK2MSFTNGP14.phx.gbl...
>> >> 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" <Corless.1ufc2c_1125147919.2281@excelforum-nospam.com> wrote
> in
>> >> message news:Corless.1ufc2c_1125147919.2281@excelforum-nospam.com...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[RICOUK]

Thanks for the help everyone.