CODEWITHSUNDEEP
vbaexcelborder
user3779627
user3779627

Reputation: 57

How can I use VBA to dynamically border my columns?

I'm currently working with Excel and here's what I'd like to do. When pasting a table in Excel, I would like for that table to appear with borders to the left of all columns. I was wondering what would be the code to do this. Here is what I have so far:

'affichage des cadres autour des cases et colonnes
Dim lastColumnNumber As Long
lastColumnNumber = Cells(1, Columns.Count).End(xlToLeft).Column
Dim i As Integer
i = 0
Dim col As String
col = "A"
Do While i <= lastColumnNumber
    With Columns("col:col").Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = 1
    End With
    col = Mid(Range(col & 1).Offset(, 1).Address, 2, 1)
    i = i + 1
Loop

Which doesn't fire up any errors but doesn't work either. If I try this instead:

'affichage des cadres autour des cases et colonnes
    With Columns("A:D").Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = 1
    End With

I get that only column D has a left border (not even A through D). That's why I tried the loop.

Upvotes: 0

Views: 1991

Answers (1)

Mark Balhoff
Mark Balhoff

Reputation: 2356

Your code doesn't do anything that you see because your data is not in column COL. You used the text string "COL" instead of the value of the variable col. The following:

With Columns("col:col").Borders(xlEdgeRight)

Should be:

With Columns(col & ":" & col).borders(xlEdgeRight)

Though I'm not completely sure why you're bothering to get the column letter instead of say:

With Columns(i + 1).borders(xlEdgeRight)

Upvotes: 1

Related Questions