Thanks Rowan, this worked great! selecting groups to add border- now with a page break
"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
|