excel 在VBA中基于合并单元格的第一行向表格添加外部边框

bihw5rsg 于 2023-03-13 发布在其他

关注(0)|答案(1)|浏览(157)

我有一个excel表，如下所示：

我尝试根据第一行添加外部边框，以获得以下输出：

我尝试了以下方法，但是不起作用。虽然我没有收到错误，但是Excel在运行VBA时会变得没有响应（20分钟），并且必须强制退出应用程序。我认为它运行到了一个无限循环（不确定）。

Sub shopBorder()
    With ActiveSheet
        Dim rng As Range, cell As Range, borderRange As Range
        Set rng = .Range("A1", .Range("A" & .Columns.Count).End(xlToRight))
        
        For Each cell In rng
            If cell.MergeArea(1).Address = cell.Address Then
                Set borderRange = .Range(cell.MergeArea, cell.End(xlDown).End(xlDown).End(xlDown))
                AddBorder borderRange
            End If
        Next Cell
   End With
End Sub

Public Function AddBorder(rng As Range)
    rng.BorderAround _
        LineStyle:=xlContinuous, _
        Weight:=xlMedium
End Function

excel

来源：https://stackoverflow.com/questions/75699077/adding-outside-border-to-a-table-based-on-the-first-row-that-has-merged-cells-in

1条答案

按热度按时间

bweufnob1#

如果您尝试以下操作：

ActiveSheet.Range("A1", ActiveSheet.Range("A" & ActiveSheet.Columns.Count).End(xlToRight)).Interior.Color = vbRed

您将立即看到将rng设置为该值的问题
请尝试以下操作：

Sub shopBorder()
        Dim rng As Range, cell As Range, borderRange As Range
    
    With ActiveSheet
        Set rng = .Range("A1").Resize(1, .UsedRange.Columns.Count)
        For Each cell In rng
            i = cell.Address
            j = cell.MergeArea(1).Address
            If cell.MergeArea(1).Address = cell.Address Then
                Set borderRange = .Range(cell.MergeArea, cell.End(xlDown).End(xlDown))
                AddBorder borderRange
            End If
        Next cell
   End With
   
End Sub

赞(0）回复(0）举报 2023-03-13

我来回答

excel 在VBA中基于合并单元格的第一行向表格添加外部边框

1条答案

相关问题

热门标签

最新问答