VBA - 设置不同列和行的侧边框

VBA - 设置不同列和行的侧边框

我每天都会运行一个列表,它总是不同的。每天都会有不同数量的行,其中包含不同的值。

列表 1 清单 2

我已经对其进行了编码,因此我拥有完全符合我要求的水平边框,但现在我需要垂直(侧面 - 左侧和右侧)边框。我希望在包含文本或某种类型的值的每个单元格、包括第 1 行之后的每一列和每一行中都有细边框。同样,行和列的数量每天都会有所不同,因此代码能够考虑到这一点很重要。

Option Explicit
Sub Borders()
    Dim WS As Worksheet
    Dim dataRange As Range
    Dim v As Variant, I As Long

Set WS = ActiveWorkbook.Worksheets("Sheet1")

With WS
    Set dataRange = Range(.Cells(2, 1), Cells(.Rows.Count, 10).End(xlUp))
    Set dataRange = dataRange.Resize(rowsize:=dataRange.Rows.Count + 1)
    With dataRange
        v = .Columns(10)
        For I = 1 To UBound(v) - 1
            If v(I, 1) <> v(I + 1, 1) Then
                With .Rows(I).Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Color = vbBlack
                    .Weight = xlMedium
                 End With
            Else
                .Rows(I).Borders(xlEdgeBottom).LineStyle = 1 'Default Border
            End If
        Next I
    End With
End With

'This is where I'd like the code for the vertical borders to go

End Sub

我希望最终的产品看起来像这样。 完成品

答案1

这将为使用范围内第 1 行之后的所有单元格设置垂直边框。

'Sets vertical borders
Dim iRange As Range
Dim iCells As Range

Set iRange = ActiveWorkbook.Worksheets("Sheet1").UsedRange.Offset(1, 1)

For Each iCells In iRange
    If Not IsEmpty(iCells) Then
        With iCells.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
        End With
        With iCells.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
        End With
    End If
Next iCells

相关内容