Is there a way to put a border around cells not arranged in a rectangle?-CodePudding

I'm trying to make a sort of table, but instead of all the columns ending in the same row, the border should be just underneath the last item in the table.

An example would be:

At the moment, I'm doing this manually by drawing borders around each of the columns, but this doesn't seem to be the most efficient way of doing this. Is there was a way to make it so that the borders surround the outside of the block of cells, perhaps using conditional formatting?

Ideally, this would allow me to add another element to one of the columns and the border would automatically accommodate for this.

CodePudding user response：

Have you tried creating a macro for it? There is Range.Borders property you could utilize. If you're not familiar with writing macros, you can also find "Record Macro" button. Use Ctrl Arrow shortcut to go to the far sides of the tables - macro will record this as such.

CodePudding user response：

Some VBA could solve this (I don't think there is any great way to do this in the front-end/UI besides the tedious way you've been doing it.

Something like the following would get you in the ballpark:

Sub determineBorders()
    Dim rngCell As Range
    
    For Each rngCell In Sheet1.UsedRange
        
        'Determine if we are at a side
        leftSide = rngCell.Offset(0, -1) = "" And Not rngCell = "" Or (Not rngCell.Offset(0, -1) = "" And rngCell = "")
        topSide = (rngCell.Offset(-1, 0) = "" And Not rngCell = "") Or (Not rngCell.Offset(-1, 0) = "" And rngCell = "")
        rightSide = rngCell.Offset(0, 1) = "" And Not rngCell = "" Or (Not rngCell.Offset(0, 1) = "" And rngCell = "")
        bottomSide = rngCell.Offset(1, 0) = "" And Not rngCell = "" Or (Not rngCell.Offset(1, 0) = "" And rngCell = "")
       
        'Remove any borders that don't exists
        If Not leftSide Then rngCell.Borders(xlEdgeLeft).LineStyle = xlNone
        If Not topSide Then rngCell.Borders(xlEdgeTop).LineStyle = xlNone
        If Not rightSide Then rngCell.Borders(xlEdgeRight).LineStyle = xlNone
        If Not bottomSide Then rngCell.Borders(xlEdgeBottom).LineStyle = xlNone
        
        'Border up appropriate sides based on learnings above
        If leftSide Then paintThisBorder rngCell, XlBordersIndex.xlEdgeLeft
        If topSide Then paintThisBorder rngCell, XlBordersIndex.xlEdgeTop
        If rightSide Then paintThisBorder rngCell, XlBordersIndex.xlEdgeRight
        If bottomSide Then paintThisBorder rngCell, XlBordersIndex.xlEdgeBottom
        
    Next rngCell
    
End Sub

Sub paintThisBorder(rngCell As Range, enumBorderLoc As XlBordersIndex)
    'You can change how the borders look by monkeying in this sub. Right now they are thin red lines.
    rngCell.Borders(enumBorderLoc).LineStyle = xlContinuous
    rngCell.Borders(enumBorderLoc).ColorIndex = 3
End Sub