Vba to draw border on single column

VeryForgetful

Board Regular
Joined
Mar 1, 2015
Messages
242
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi,

This should put a border round a range of cells in a column. I used column D but you can change that by changing the first line of the code.

I did not use "UsedRange" because the results were unreliable. I found the first and last used cells in the column instead.
The border comprises a thick line round the outside of the range. You may need to change that. Probably the easiest way to work out the settings you need is to record making the changes then copy the settings from the recorded macro into the code below.

Code:
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
    
    With Range(Cells(fr, strCol), Cells(lr, strCol))
       .Borders(xlDiagonalDown).LineStyle = xlNone
       .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThick
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThick
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThick
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThick
        End With
       .Borders(xlInsideVertical).LineStyle = xlNone
       .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With

End Sub
 
Upvote 0
Rick,

Thanks for this.

I had to remove the TintAndShade as I assume this isn't compatible with Excel 2003? Also I changed xlThick to xlThin.

How would I amend this so that every single cell in the range gets a thin border? I tried to record a macro but I ended up with every cell missing the bottom border despite defining it.

Also, do I need to declare something to clear the border at the start of this code, for example range("D:D").clearformats in case the range of cells in the column was to get smaller?

Thanks
 
Upvote 0
Hi,

This is probably a neater way to do it.

Rather than clear all the formats I have just cleared the borders.

Then the border styles are processed in a loop.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Const strCol As String = "D" ' <-- Change column letter here
    Dim arrBorders As Variant
    
    Dim fr As Long
    Dim lr As Long
    Dim Border As Variant
    
    fr = Cells(1, strCol).End(xlDown).Row
    lr = Cells(Rows.Count, strCol).End(xlUp).Row
    
    arrBorders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideHorizontal)
    
    Range("D:D").Borders.LineStyle = xlNone

    With Range(Cells(fr, strCol), Cells(lr, strCol))
        For Each Border In arrBorders
            With .Borders(Border)
                .LineStyle = xlContinuous
                .ColorIndex = xlAutomatic
                .Weight = xlThin
            End With
        Next
    End With

End Sub
 
Upvote 0
If the settings are as in Ricks code you can probably get away with just

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Range("D:D").Borders.LineStyle = xlNone
  Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row).Borders.LineStyle = xlThin
End Sub
 
Upvote 0
If the settings are as in Ricks code you can probably get away with just

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Range("D:D").Borders.LineStyle = xlNone
  Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row).Borders.LineStyle = xlThin
End Sub
Your code assumes the data always starts on Row 1... that is not a given. Also, when I try your code on my system, the LineStyle defaults to as dotted line.



This is probably a neater way to do it.

Rather than clear all the formats I have just cleared the borders.

Then the border styles are processed in a loop.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Const strCol As String = "D" ' <-- Change column letter here
    Dim arrBorders As Variant
    
    Dim fr As Long
    Dim lr As Long
    Dim Border As Variant
    
    fr = Cells(1, strCol).End(xlDown).Row
    lr = Cells(Rows.Count, strCol).End(xlUp).Row
    
    arrBorders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideHorizontal)
    
    Range("D:D").Borders.LineStyle = xlNone

    With Range(Cells(fr, strCol), Cells(lr, strCol))
        For Each Border In arrBorders
            With .Borders(Border)
                .LineStyle = xlContinuous
                .ColorIndex = xlAutomatic
                .Weight = xlThin
            End With
        Next
    End With

End Sub
One problem with your code... if you select the entire column and clear it, you end up with every cell in Column D as blank with a border. Here is a different way to write the code that eliminates the loop and does not suffer from the "column clear" problem...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  On Error GoTo NoFilledCells
  Columns("D").Borders.LineStyle = xlNone
  With Range(Columns("D").SpecialCells(xlConstants)(1), Cells(Rows.Count, "D").End(xlUp))
    .BorderAround xlContinuous, xlThin
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).Weight = xlThin
  End With
NoFilledCells:
End Sub
 
Last edited:
Upvote 0
the LineStyle defaults to as dotted line.


Sorry, got carried away in my last post. Should have read..

Code:
Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row).Borders.[B][/B][COLOR="#FF0000"]Weight[/COLOR] = xlThin


Your code assumes the data always starts on Row 1...

If the OP refers to a column and not a range then I take it that it does mean the entire column unless told otherwise and so I do assume it starts in Row 1.
 
Last edited:
Upvote 0
Your code assumes the data always starts on Row 1... that is not a given. Also, when I try your code on my system, the LineStyle defaults to as dotted line
If the OP refers to a column and not a range then I take it that it does mean the entire column unless told otherwise and so I do assume it starts in Row 1.
I can understand your making that assumption, however, you posted your code as a follow-up/alternative to RickXL's code, but he does not make that assumption... his code calculates the first used cell and starts his bordering from it on downward... so, unless cell D1 is filled, you code will produce a different result from RickXL's.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top