VBA边框Excel依赖于页面大小 [英] VBA Borders Excel Dependent on Page Size

问题描述

我想根据页面的大小来创建我的每个excel页面的边框，例如行数和列数可以随时变化。我试过这个，但它是特定的单元格

  Sub AddBorders（）
 
带范围（B8 ：I10）
 .Borders（xlEdgeLeft）.LineStyle = xlContinuous 
 .Borders（xlEdgeRight）.LineStyle = xlContinuous 
 .Borders（xlEdgeBottom）.LineStyle = xlContinuous 
 .Borders xlEdgeTop）.LineStyle = xlContinuous End With End Sub 
  

解决方案

code> UsedRange 应该永远用于查找具有数据的最后一个单元格。这是非常不可靠的。 。您可能需要查看

I want to create borders around each of my excel pages dependent on the size of the page e.g. number of rows and columns can vary all the time. I've tried this but it's specific cells

 Sub AddBorders()

 With Range("B8:I10")
     .Borders(xlEdgeLeft).LineStyle = xlContinuous
     .Borders(xlEdgeRight).LineStyle = xlContinuous
     .Borders(xlEdgeBottom).LineStyle = xlContinuous
     .Borders(xlEdgeTop).LineStyle = xlContinuous End With End Sub

解决方案

UsedRange should NEVER be used to find the last cell which has data. It is highly unreliable. . You may want to see THIS for the explanation on usedrange.

Always find the last row and the last column and then create the range. See this example.

I would recommend this way

Sub AddBorders()
    Dim ws As Worksheet
    Dim lastrow As Long, lastcol As Long

    Set ws = Sheet1 '<~~ Change as applicable

    With ws
        '~~> Check if sheet has any data
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            '~~> Get Last Row
            lastrow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row

            '~~> Get Last Column
            lastcol = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Column

            '~~> Work with the range
            .Range(.Cells(1, 1), .Cells(lastrow, lastcol)).BorderAround _
            xlContinuous, xlMedium

        End If
    End With
End Sub

FOLLOWUP FROM COMMENTS

This works better. Only issue is border doesn't go around any graphs/charts. Is there a way to do that aswell? Thanks for your help – user1296762 7 mins ago

Also sorry can we have the bottom border last row+1 as some rows are grouped up and therefore line can't be seen if not expanded – user1296762 5 mins ago

Is this what you are trying?

Sub AddBorders()
    Dim ws As Worksheet
    Dim lastrow As Long, lastcol As Long
    Dim shp As Shape

    Set ws = Sheet1 '<~~ Change as applicable

    With ws
        '~~> Check if sheet has any data
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            '~~> Get Last Row
            lastrow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row

            '~~> Get Last Column
            lastcol = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Column


        End If

        '~~> Loop through shapes and find the last row and column
        For Each shp In .Shapes
            If shp.BottomRightCell.Row > lastrow Then lastrow = shp.BottomRightCell.Row
            If shp.BottomRightCell.Column > lastcol Then lastcol = shp.BottomRightCell.Column
        Next

        If lastrow <> 0 And lastcol <> 0 Then
            'Also sorry can we have the bottom border last row+1 as some rows are
            'grouped up and therefore line can't be seen if not expanded
            '–  user1296762 2 mins ago
            lastrow = lastrow + 1: lastcol = lastcol + 1

            '~~> Work with the range
            .Range(.Cells(1, 1), .Cells(lastrow, lastcol)).BorderAround _
            xlContinuous, xlMedium
        End If
    End With
End Sub

Sceenshot

这篇关于VBA边框Excel依赖于页面大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！