Hi,

I'm so new to VBA that I'm still at the recording macro stage then trying to
edit the macro! I have about 6 columns of data and about 1000 rows. I want
to put borders around those sections that should go together according to
Loop number. Here's a sample (sorted on the second column), I don't know how
well the columns will align after I send.

Loop 5J ANG data data data data
Loop 5J ANG data data data data
Loop 5J ANG data data data data

Loop 142 ANG data data data data

Loop 23 JES data data data data
Loop 23 JES data data data data

Above, I entered blank rows to separate the data to indicate the groups that
should have borders around them. In this example there would be three
separate borders around the three groups.

The criteria is that anytime there is a new Loop number, it begins a new
group that should be inside a common border.

Is this a simple job to write VBA that would border these groups in the 1000
rows according to the Loop criteria above? Any suggestions?

Many thanks,

Harold

Assume Loop labels start in A2 and you want 6 columns bordered (change the 6
in the code below to reflect # of columns).

Sub AA()
Dim StartRow As Range
Dim lastrow As Long
Dim i As Long
Set StartRow = Range("A2")
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
StartRow.Resize(i - StartRow.Row + 1, 6).BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlThin
Set StartRow = Cells(i + 1, 1)
End If
Next

End Sub

--
Regards,
Tom Ogilvy




"Harold Good" wrote in message
...
Hi,

I'm so new to VBA that I'm still at the recording macro stage then trying
to
edit the macro! I have about 6 columns of data and about 1000 rows. I want
to put borders around those sections that should go together according to
Loop number. Here's a sample (sorted on the second column), I don't know
how
well the columns will align after I send.

Loop 5J ANG data data data data
Loop 5J ANG data data data data
Loop 5J ANG data data data data

Loop 142 ANG data data data data

Loop 23 JES data data data data
Loop 23 JES data data data data

Above, I entered blank rows to separate the data to indicate the groups
that
should have borders around them. In this example there would be three
separate borders around the three groups.

The criteria is that anytime there is a new Loop number, it begins a new
group that should be inside a common border.

Is this a simple job to write VBA that would border these groups in the
1000
rows according to the Loop criteria above? Any suggestions?

Many thanks,

Harold

Wow, thanks Tom, this worked perfectly. Now I just need to work through this
and figure out what its doing. I sure appreciate your kind help!

Harold
=============================
"Tom Ogilvy" wrote in message
...
Assume Loop labels start in A2 and you want 6 columns bordered (change the
6
in the code below to reflect # of columns).

Sub AA()
Dim StartRow As Range
Dim lastrow As Long
Dim i As Long
Set StartRow = Range("A2")
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
StartRow.Resize(i - StartRow.Row + 1, 6).BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlThin
Set StartRow = Cells(i + 1, 1)
End If
Next

End Sub

--
Regards,
Tom Ogilvy




"Harold Good" wrote in message
...
Hi,

I'm so new to VBA that I'm still at the recording macro stage then trying
to
edit the macro! I have about 6 columns of data and about 1000 rows. I
want
to put borders around those sections that should go together according to
Loop number. Here's a sample (sorted on the second column), I don't know
how
well the columns will align after I send.

Loop 5J ANG data data data data
Loop 5J ANG data data data data
Loop 5J ANG data data data data

Loop 142 ANG data data data data

Loop 23 JES data data data data
Loop 23 JES data data data data

Above, I entered blank rows to separate the data to indicate the groups
that
should have borders around them. In this example there would be three
separate borders around the three groups.

The criteria is that anytime there is a new Loop number, it begins a new
group that should be inside a common border.

Is this a simple job to write VBA that would border these groups in the
1000
rows according to the Loop criteria above? Any suggestions?

Many thanks,

Harold

This is great and works perfectly. But upon looking at the results, I'd like
not just a border around the outside, but also a thin vertical border
between each column. I guess I could do it manually down the entire column,
but I'm sure it wouldn't be hard to add that to this code below.

Thanks to Tom or anyone that can add that for me.

Harold

=================
"Tom Ogilvy" wrote in message
...
Assume Loop labels start in A2 and you want 6 columns bordered (change the
6
in the code below to reflect # of columns).

Sub AA()
Dim StartRow As Range
Dim lastrow As Long
Dim i As Long
Set StartRow = Range("A2")
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
StartRow.Resize(i - StartRow.Row + 1, 6).BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlThin
Set StartRow = Cells(i + 1, 1)
End If
Next

End Sub

--
Regards,
Tom Ogilvy




"Harold Good" wrote in message
...
Hi,

I'm so new to VBA that I'm still at the recording macro stage then trying
to
edit the macro! I have about 6 columns of data and about 1000 rows. I
want
to put borders around those sections that should go together according to
Loop number. Here's a sample (sorted on the second column), I don't know
how
well the columns will align after I send.

Loop 5J ANG data data data data
Loop 5J ANG data data data data
Loop 5J ANG data data data data

Loop 142 ANG data data data data

Loop 23 JES data data data data
Loop 23 JES data data data data

Above, I entered blank rows to separate the data to indicate the groups
that
should have borders around them. In this example there would be three
separate borders around the three groups.

The criteria is that anytime there is a new Loop number, it begins a new
group that should be inside a common border.

Is this a simple job to write VBA that would border these groups in the
1000
rows according to the Loop criteria above? Any suggestions?

Many thanks,

Harold

This should get you started:

Sub AA()
Dim StartRow As Range
Dim lastrow As Long
Dim i As Long
Set StartRow = Range("A2")
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
With StartRow.Resize(i - StartRow.Row + 1, 6)
.BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlThin
.Borders(xlInsideVertical).Weight = _
xlHairline
End With
Set StartRow = Cells(i + 1, 1)
End If
Next

End Sub

If that isn't the line you want in the interior columns, then experiment
with xlThin for that and make the outer boarder xlMedium or xlThick
Or play with other attributes such as colorindex and linestyle.

--
Regards,
Tom Ogilvy
"Harold Good" wrote in message
...
This is great and works perfectly. But upon looking at the results, I'd
like
not just a border around the outside, but also a thin vertical border
between each column. I guess I could do it manually down the entire
column,
but I'm sure it wouldn't be hard to add that to this code below.

Thanks to Tom or anyone that can add that for me.

Harold

=================
"Tom Ogilvy" wrote in message
...
Assume Loop labels start in A2 and you want 6 columns bordered (change
the
6
in the code below to reflect # of columns).

Sub AA()
Dim StartRow As Range
Dim lastrow As Long
Dim i As Long
Set StartRow = Range("A2")
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
StartRow.Resize(i - StartRow.Row + 1, 6).BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlThin
Set StartRow = Cells(i + 1, 1)
End If
Next

End Sub

--
Regards,
Tom Ogilvy




"Harold Good" wrote in message
...
Hi,

I'm so new to VBA that I'm still at the recording macro stage then
trying
to
edit the macro! I have about 6 columns of data and about 1000 rows. I
want
to put borders around those sections that should go together according
to
Loop number. Here's a sample (sorted on the second column), I don't
know
how
well the columns will align after I send.

Loop 5J ANG data data data data
Loop 5J ANG data data data data
Loop 5J ANG data data data data

Loop 142 ANG data data data data

Loop 23 JES data data data data
Loop 23 JES data data data data

Above, I entered blank rows to separate the data to indicate the groups
that
should have borders around them. In this example there would be three
separate borders around the three groups.

The criteria is that anytime there is a new Loop number, it begins a
new
group that should be inside a common border.

Is this a simple job to write VBA that would border these groups in the
1000
rows according to the Loop criteria above? Any suggestions?

Many thanks,

Harold

This is great and works perfectly. But upon looking at the results, I'd
like
not just a border around the outside, but also a thin vertical border
between each column. I guess I could do it manually down the entire column,
but I'm sure it wouldn't be hard to add that to this code below.

Thanks to Tom or anyone that can add that for me to the code below.

Harold

=================
"Tom Ogilvy" wrote in message
...
Assume Loop labels start in A2 and you want 6 columns bordered (change
the 6
in the code below to reflect # of columns).

Sub AA()
Dim StartRow As Range
Dim lastrow As Long
Dim i As Long
Set StartRow = Range("A2")
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
StartRow.Resize(i - StartRow.Row + 1, 6).BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlThin
Set StartRow = Cells(i + 1, 1)
End If
Next

End Sub

--
Regards,
Tom Ogilvy




"Harold Good" wrote in message
...
Hi,

I'm so new to VBA that I'm still at the recording macro stage then
trying
to
edit the macro! I have about 6 columns of data and about 1000 rows. I
want
to put borders around those sections that should go together according
to
Loop number. Here's a sample (sorted on the second column), I don't know
how
well the columns will align after I send.

Loop 5J ANG data data data data
Loop 5J ANG data data data data
Loop 5J ANG data data data data

Loop 142 ANG data data data data

Loop 23 JES data data data data
Loop 23 JES data data data data

Above, I entered blank rows to separate the data to indicate the groups
that
should have borders around them. In this example there would be three
separate borders around the three groups.

The criteria is that anytime there is a new Loop number, it begins a new
group that should be inside a common border.

Is this a simple job to write VBA that would border these groups in the
1000
rows according to the Loop criteria above? Any suggestions?

Many thanks,

Harold

Hi Harold

Tom's last reply does just that:

Sub AA()
Dim StartRow As Range
Dim lastrow As Long
Dim i As Long
Set StartRow = Range("A2")
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
With StartRow.Resize(i - StartRow.Row + 1, 6)
.BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlThin
.Borders(xlInsideVertical).Weight = _
xlHairline
End With
Set StartRow = Cells(i + 1, 1)
End If
Next

End Sub

You may not be able to see the Hairline borders on the screen but if you
printpreview they should be apparent.
If you want to see the borders more clearly on the screen then change
xlHairline to xlThin.

Hope this helps
Rowan

Harold Good wrote:
This is great and works perfectly. But upon looking at the results, I'd
like
not just a border around the outside, but also a thin vertical border
between each column. I guess I could do it manually down the entire column,
but I'm sure it wouldn't be hard to add that to this code below.

Thanks to Tom or anyone that can add that for me to the code below.

Harold

Thanks Rowan, I never did see that second reply from Tom. It works great. I
so much appreciate it!!!

One last addition would be most helpful.

In my initial request at the very bottom, note the second column (ANG and
JES, and 20 others in that column) . Would it be possible to insert a Page
Break after the end of each grouping. They're sorted on that second column,
so even if ANG only runs for half a page, I'd like to insert a Page Break
after the rows with ANG in it.

Any help would be most appreciated.

Harold

=======================
"Rowan Drummond" wrote in message
...
Hi Harold

Tom's last reply does just that:

Sub AA()
Dim StartRow As Range
Dim lastrow As Long
Dim i As Long
Set StartRow = Range("A2")
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
With StartRow.Resize(i - StartRow.Row + 1, 6)
.BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlThin
.Borders(xlInsideVertical).Weight = _
xlHairline
End With
Set StartRow = Cells(i + 1, 1)
End If
Next

End Sub

You may not be able to see the Hairline borders on the screen but if you
printpreview they should be apparent.
If you want to see the borders more clearly on the screen then change
xlHairline to xlThin.

Hope this helps
Rowan
===============================
Hi,

I'm so new to VBA that I'm still at the recording macro stage then
trying
to
edit the macro! I have about 6 columns of data and about 1000 rows. I
want
to put borders around those sections that should go together according
to
Loop number. Here's a sample (sorted on the second column), I don't know
how
well the columns will align after I send.

Loop 5J ANG data data data data
Loop 5J ANG data data data data
Loop 5J ANG data data data data

Loop 142 ANG data data data data

Loop 23 JES data data data data
Loop 23 JES data data data data

Above, I entered blank rows to separate the data to indicate the groups
that
should have borders around them. In this example there would be three
separate borders around the three groups.

The criteria is that anytime there is a new Loop number, it begins a new
group that should be inside a common border.

Is this a simple job to write VBA that would border these groups in the
1000
rows according to the Loop criteria above? Any suggestions?

Many thanks,

Harold

Hi Harold

Try:

Sub AA()
Dim StartRow As Range
Dim lastrow As Long
Dim i As Long
Set StartRow = Range("A2")
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
With StartRow.Resize(i - StartRow.Row + 1, 6)
.BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlThin
.Borders(xlInsideVertical).Weight = _
xlHairline
End With
Set StartRow = Cells(i + 1, 1)
End If
If i 2 And Cells(i, 2).Value < Cells(i - 1, 2).Value Then
Rows(i).PageBreak = xlPageBreakManual
End If
Next

End Sub

Hope this helps
Rowan

Harold Good wrote:
Thanks Rowan, I never did see that second reply from Tom. It works great. I
so much appreciate it!!!

One last addition would be most helpful.

In my initial request at the very bottom, note the second column (ANG and
JES, and 20 others in that column) . Would it be possible to insert a Page
Break after the end of each grouping. They're sorted on that second column,
so even if ANG only runs for half a page, I'd like to insert a Page Break
after the rows with ANG in it.

Any help would be most appreciated.

Harold

=======================
"Rowan Drummond" wrote in message
...

Hi Harold

Tom's last reply does just that:

Sub AA()
Dim StartRow As Range
Dim lastrow As Long
Dim i As Long
Set StartRow = Range("A2")
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
With StartRow.Resize(i - StartRow.Row + 1, 6)
.BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlThin
.Borders(xlInsideVertical).Weight = _
xlHairline
End With
Set StartRow = Cells(i + 1, 1)
End If
Next

End Sub

You may not be able to see the Hairline borders on the screen but if you
printpreview they should be apparent.
If you want to see the borders more clearly on the screen then change
xlHairline to xlThin.

Hope this helps
Rowan

===============================

Hi,

I'm so new to VBA that I'm still at the recording macro stage then
trying

to

edit the macro! I have about 6 columns of data and about 1000 rows. I
want
to put borders around those sections that should go together according
to
Loop number. Here's a sample (sorted on the second column), I don't know

how

well the columns will align after I send.

Loop 5J ANG data data data data
Loop 5J ANG data data data data
Loop 5J ANG data data data data

Loop 142 ANG data data data data

Loop 23 JES data data data data
Loop 23 JES data data data data

Above, I entered blank rows to separate the data to indicate the groups

that

should have borders around them. In this example there would be three
separate borders around the three groups.

The criteria is that anytime there is a new Loop number, it begins a new
group that should be inside a common border.

Is this a simple job to write VBA that would border these groups in the

1000

rows according to the Loop criteria above? Any suggestions?

Many thanks,

Harold

"Rowan Drummond" wrote in message
...
Hi Harold

Try:

Sub AA()
Dim StartRow As Range
Dim lastrow As Long
Dim i As Long
Set StartRow = Range("A2")
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
With StartRow.Resize(i - StartRow.Row + 1, 6)
.BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlThin
.Borders(xlInsideVertical).Weight = _
xlHairline
End With
Set StartRow = Cells(i + 1, 1)
End If
If i 2 And Cells(i, 2).Value < Cells(i - 1, 2).Value Then
Rows(i).PageBreak = xlPageBreakManual
End If
Next

End Sub

Hope this helps
Rowan

Harold Good wrote:
Thanks Rowan, I never did see that second reply from Tom. It works great.
I so much appreciate it!!!

One last addition would be most helpful.

In my initial request at the very bottom, note the second column (ANG and
JES, and 20 others in that column) . Would it be possible to insert a
Page Break after the end of each grouping. They're sorted on that second
column, so even if ANG only runs for half a page, I'd like to insert a
Page Break after the rows with ANG in it.

Any help would be most appreciated.

Harold

=======================
"Rowan Drummond" wrote in message
...

Hi Harold

Tom's last reply does just that:

Sub AA()
Dim StartRow As Range
Dim lastrow As Long
Dim i As Long
Set StartRow = Range("A2")
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Cells(i, 1).Value < Cells(i + 1, 1).Value Then
With StartRow.Resize(i - StartRow.Row + 1, 6)
.BorderAround _
ColorIndex:=xlAutomatic, Weight:=xlThin
.Borders(xlInsideVertical).Weight = _
xlHairline
End With
Set StartRow = Cells(i + 1, 1)
End If
Next

End Sub

You may not be able to see the Hairline borders on the screen but if you
printpreview they should be apparent.
If you want to see the borders more clearly on the screen then change
xlHairline to xlThin.

Hope this helps
Rowan

===============================

Hi,

I'm so new to VBA that I'm still at the recording macro stage then
trying

to

edit the macro! I have about 6 columns of data and about 1000 rows. I
want
to put borders around those sections that should go together according
to
Loop number. Here's a sample (sorted on the second column), I don't
know

how

well the columns will align after I send.

Loop 5J ANG data data data data
Loop 5J ANG data data data data
Loop 5J ANG data data data data

Loop 142 ANG data data data data

Loop 23 JES data data data data
Loop 23 JES data data data data

Above, I entered blank rows to separate the data to indicate the groups

that

should have borders around them. In this example there would be three
separate borders around the three groups.

The criteria is that anytime there is a new Loop number, it begins a
new
group that should be inside a common border.

Is this a simple job to write VBA that would border these groups in the

1000

rows according to the Loop criteria above? Any suggestions?

Many thanks,

Harold

You're welcome!