border round a table

[methody]

Hello I have a range of cells - a sports league table - with formulas in it from AW6 to BF50.
The cells in AW are linked to another part of the sheet where teams are input. The formula in AW6 is IF(AG6="","",AG6)) - So teams are entered in AG6 and below
If there is one team, AW6 will not be "" and all the cells across will not be "".
If there are two teams, AW6 & AW7 will not be "" and all the cells across will not be "". etc

So the number of teams in the AW column determines the height of the table.

I am looking for a piece of code which will put a simple border round the sides and bottom of the table.
So as you go down AW. At the point where you first find "", put a border along the bottom and up the side.

I would prefer code as opposed to conditional formatting if possible.
thank you

 

[Scott Huish]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Set c = Intersect(Target, Range("AW6:AW50"))
If c Is Nothing Then Exit Sub
With c.Resize(, 10)
    .Borders.LineStyle = xlNone
    .BorderAround xlSolid, xlThin
    .Borders(xlEdgeTop).LineStyle = xlNone
End With
End Sub

 

[methody]

Hello Hotpepper
Thank you for that.
It does what I asked although I am trying to simply fit it onto to another macro as opposed to that worksheet event. Do I need to change this line?
Set c = Intersect(Target, Range("AW6:AW50"))

thank you

 

[Scott Huish]

Sub test()
Dim x As Long
x = Range("AW50").End(xlUp).Row
If x < 6 Then x = 6
With Range("AW" & x).Resize(, 10)
    .Borders.LineStyle = xlNone
    .BorderAround xlSolid, xlThin
    .Borders(xlEdgeTop).LineStyle = xlNone
End With
End Sub

 

[methody]

Hello Hotpepper
sorry but this doesn't seem to work . It continually seems to treat the table as if it has one non empty row now matter how many teams there are.

thanks

 

[methody]

I think it works if the cells in AW are completely empty but not if they have formula which give "".

 

[Scott Huish]

What is the formula in these cells?

 

[methody]

IF(AG6="","",AG6))
where AG is where the teams are typed in.
thnak you

 

[Scott Huish]

Then all you need to do is base the end on that range:

Sub test()
Dim x As Long
x = Range("AG50").End(xlUp).Row
If x < 6 Then x = 6
With Range("AW" & x).Resize(, 10)
    .Borders.LineStyle = xlNone
    .BorderAround xlSolid, xlThin
    .Borders(xlEdgeTop).LineStyle = xlNone
End With
End Sub

 

[methody]

thank you. That's perfect