10613
Question:
Wondering if there's a way to set both the left and right borders of a cell with a single statement? Something akin to the way msgBox configurations can be combined/added together (e.g. vbYesNo + vbQuestion). I tried:
Cells(j, i).Borders(xlEdgeLeft + xlEdgeRight)
Which leads to an error for me. It's a bit duplicative to code each border individually...
Here what I've come up with:
For i = 1 To 10
For j = 2 To 6 + numAcft
Cells(j, i) = "Week Start Date"
With Cells(j, i).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Cells(j, i).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
...
...
Is there a more elegant way?
<hr />With full credit to @egan-wolf and @robinmackenzie here is the full solution I used to answer the above question. As suggested I created a helper function and passed it the cell I want to set the borders for and the line style & weight I'd like them to be, turning 8 lines of code into a much more readable single line:
setLeftAndRightEdges Cells(j, i), xlContinuous, xlMedium
Private Sub setLeftAndRightEdges(ByVal cell As Range, ByVal lineStyle As Long, ByVal weight As Long)
Dim edges(1) As Variant
Dim edge As Variant
edges(0) = xlEdgeLeft
edges(1) = xlEdgeRight
For Each edge In edges
cell.Borders(edge).LineStyle = lineStyle
cell.Borders(edge).weight = weight
Next edge
End Sub
Answer1:Not sure if I would call it more elegant way, but this is the option to not duplicate code
Dim edges(1) As Variant
edges(0) = xlEdgeLeft
edges(1) = xlEdgeRight
For Each edge In edges
ActiveCell.Borders(edge).LineStyle = xlContinuous
Next edge
Answer2:How about:
With Cells(j, i)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
Using this you don't need an additional for loop.
There are many ways to skin a cat, as they say.. how about:
Dim edge As Variant
For edge = xlEdgeLeft To xlEdgeRight Step xlEdgeRight - xlEdgeLeft
Cells(j, i).Borders(edge).LineStyle = xlContinuous
Next
Answer4:Here's a slightly hacky way to do it as a one-liner where rng is a Range object we have already defined:
rng.Offset(0, -1).Resize(1, 3).Borders(xlInsideVertical).LineStyle = xlContinuous
The trick is that you want to get a range that includes the left-hand and right-hand cells to the target range and then for that group of cells, set the inside vertical border. This has the effect of setting the left-hand and right-hand border of the original cell.
<ul><li>Offset(0, -1) goes to the left-hand cell of the target cell</li>
<li>Resize(1, 3) extends the range to 3 columns on the row of the target cell</li>
<li>Borders(xlInsideVertical).LineStyle... sets the format of the inner verticals of this range of 3 cells.</li>
</ul>Sample code:
Option Explicit
Sub Test()
Dim ws As Worksheet
Dim rng As Range
'sheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
'target range
Set rng = ws.Range("B8")
'one-liner to set left and right borders
rng.Offset(0, -1).Resize(1, 3).Borders(xlInsideVertical).LineStyle = xlContinuous
End Sub
Edit: as pointed out by @EganWolf this won't work for cells in column A. Or, for that matter, it won't work in the right-handmost column of the sheet. Additional coding will be required for these 'edge' cases.
