我每天都会运行一个列表,它总是不同的。每天都会有不同数量的行,其中包含不同的值。
我已经对其进行了编码,因此我拥有完全符合我要求的水平边框,但现在我需要垂直(侧面 - 左侧和右侧)边框。我希望在包含文本或某种类型的值的每个单元格、包括第 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