主页 / computer / 问题 / 1662855
Accepted
wyatt7613
Asked: 2021-07-16 15:30:01 +0800 CST

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

  • 772

每天我都会列出一个清单，它总是不同的。它每天都会有不同数量的行，其中包含不同的值。

清单 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

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

microsoft-excel vba

1 个回答

  1. Best Answer

    这将为第 1 行之后的 usedrange 中的所有单元格设置垂直边框。

    '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
    • 1

相关问题