时间戳

首页 > 解决方案 > 在 Excel 中使用 VBA 进行边框、单元格对齐和换行

excel - 在 Excel 中使用 VBA 进行边框、单元格对齐和换行

问题描述

我想将复制的单元格格式化为所有边框，单元格对齐顶部和单元格左对齐，以及换行文本。

对于我试过的边界

With rng.Borders
  .LineStyle = xlContinuous

当前宏：

Sub Copy_Data()
    Dim Src As Worksheet, Dst As Worksheet
    Dim LastRow As Long, r As Range
    Dim CopyRange As Range

    Set Src = Sheets("Template")
    Set Dst = Sheets("Report")

    LastRow = Src.Cells(Cells.Rows.Count, "B").Row

    For Each r In Src.Range("B2:B" & LastRow)
        If r.Value = "Planning" Or r.Value = "On Hold" Or r.Value = "Planning" Or r.Value = "Gathering Info" Or r.Value = "" Then
            If CopyRange Is Nothing Then
                Set CopyRange = r.EntireRow
            Else
                Set CopyRange = Union(CopyRange, r.EntireRow)
            End If
        End If
    Next r

    If Not CopyRange Is Nothing Then
        CopyRange.Copy Dst.Range("A3")
    End If

End Sub

标签： excelvba

解决方案

如果你录制一个宏，你会得到这样的东西

Range("A1:C10").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With

上面的代码也可以写成。注意我们如何使用循环来创建边框。检查 的值是多少xlEdgeLeft, xlEdgeTop, xlEdgeBottom.. etc。然后您将了解我们如何使用循环。

Dim rng As Range

'~~> Change this to whatever range you want
Set rng = Sheet1.Range("A1:B10")

With rng
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone

    For k = 7 To 12
        With .Borders(k)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
    Next
End With

同样对于换行和单元格对齐，只需记录一个宏并编辑代码以满足您的需要:)

推荐阅读