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 SubAnswer1:
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 edgeAnswer2:
With Cells(j, i) .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With
Using this you don't need an additional
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 NextAnswer4:
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>
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.