Vba to draw border on single column

[VeryForgetful]

Hi,

Would anyone have any code to draw a border around the used range of a single column?

I have a vlookup which runs in a column adjacent to a PivotTable. I have code to recreate the border around the table each time it's refreshed but I need some code to also draw a border around this additional column.

Obviously this border is going to need to change dynamically as and when the table is refreshed.

Also, would any code need to go into the PivotTableupdate event or just a worksheet change event?

Cheers

 

[MARK858]

so, unless cell D1 is filled, you code will produce a different result from RickXL's.

If matching RickXL's code then it would be

Private Sub Worksheet_Change(ByVal Target As Range)
Const strCol As String = "D"    ' <-- Change column letter here

    Dim fr As Long
    Dim lr As Long

    fr = Cells(1, strCol).End(xlDown).Row
    lr = Cells(Rows.Count, strCol).End(xlUp).Row

    On Error Resume Next
    Range("D:D").Borders.LineStyle = xlNone
    Range(Cells(fr, strCol), Cells(lr, strCol)).Borders.Weight = xlThin
    On Error GoTo 0

End Sub

As an alternative to Rick Rothstein's

Sub XXX()
    Columns("D").Borders.LineStyle = xlNone
    On Error Resume Next
    Range(Columns("D").SpecialCells(xlConstants)(1), Cells(Rows.Count, "D").End(xlUp)).Borders.Weight = xlThin
    On Error GoTo 0
End Sub