文章/答案/技术大牛

发布

社区首页 >问答首页 >在Excel中添加边框和合并单元格的宏(VBA)，如果单元格不是空的

问在Excel中添加边框和合并单元格的宏(VBA)，如果单元格不是空的
EN

Stack Overflow用户

提问于 2016-11-02 13:45:25

回答 1查看 1K关注 0票数 1

我录制了以下宏：

Sub Macro1()
Range("E66:F68").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
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D66:D68,C66:C68,B66:B68,A66:A68").Select
Range("A66").Activate
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Selection.Merge
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
End With
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
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("G73").Select
End Sub

现在，这是从E66开始的范围的记录，它基本上为选定的单元格添加边框，并将相邻列中的单元格行合并。我想要做的是添加一个条件，查看E列，并在第一个没有边框的非空单元上启动宏，并在最后一个非空单元上结束它。在我记录的宏中，第一个无边界的非空单元是E66 (意味着范围E1:E65中的单元格至少有一侧的所有边框)，最后一个非空单元格是E68 (第二行的范围是E66: F68，因为我在从E66到F68的矩形单元格中使用了外部边框，但是条件只需要对E列进行验证)。

换句话说，我需要某种从E1到Ex的循环，当它找到一个非空和都没有边界的单元时，它会将该单元格号存储为起始单元(例如Ey)。然后，当找到空单元格(例如Ez)时，循环停止，Ez之前的单元格(所以Ez-1)存储为最后一个单元格。然后，我记录的宏应该在Ey:Fz-1范围内运行。

我该怎么做？谢谢。

excel

vba

if-statement

border

conditional-statements

回答 1

Stack Overflow用户

发布于 2016-11-02 16:26:56

这也许能行。您可以调整过滤器和格式以满足您的需要。不过，对宏记录要小心。

Sub FindAreas()
    TopRange = 1
    LastRow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    For A = 1 To LastRow
        If Range("A" & A).Value <> "" _
            And Range("A" & A).Borders(xlEdgeLeft).LineStyle = xlNone _
            And Range("A" & A).Borders(xlEdgeRight).LineStyle = xlNone _
            And Range("A" & A).Borders(xlEdgeTop).LineStyle = xlNone _
            And Range("A" & A).Borders(xlEdgeBottom).LineStyle = xlNone _
                Then Contiguous = True Else Contiguous = False
        If A = LastRow Then
            Contiguous = False
            A = A + 1
        End If
        Select Case Contiguous
            Case False
                Call ApplyFormattingtoArea("A" & TopRange & ":A" & A - 1)
                TopRange = A + 1
                A = A + 1
        End Select
    Next A
End Sub

Sub ApplyFormattingtoArea(AppliedArea)
    Application.DisplayAlerts = False
    Range(AppliedArea).Merge
    Range(AppliedArea).Borders(xlInsideVertical).LineStyle = xlNone
    Range(AppliedArea).Borders(xlInsideHorizontal).LineStyle = xlNone
    With Range(AppliedArea)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    Range(AppliedArea).Borders(xlDiagonalDown).LineStyle = xlNone
    Range(AppliedArea).Borders(xlDiagonalUp).LineStyle = xlNone
    With Range(AppliedArea).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Range(AppliedArea).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Range(AppliedArea).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Range(AppliedArea).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Application.DisplayAlerts = True
End Sub

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/40381530

复制

相似问题

问在Excel中添加边框和合并单元格的宏(VBA)，如果单元格不是空的
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Excel中添加边框和合并单元格的宏(VBA)，如果单元格不是空的EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Excel中添加边框和合并单元格的宏(VBA)，如果单元格不是空的
EN