selecting groups to add border

  • Thread starter Thread starter Harold Good
  • Start date Start date
H

Harold Good

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
 
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
=============================
 
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

=================
 
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.
 
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
 
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
 
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

=======================
 
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
 
Rowan Drummond said:
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
Click to expand...
 
Back
Top