Excel宏在变量范围内的边界单元格 [英] Excel macro to border cells within variable range

问题描述

我需要使用变量行和变量列自动化导出的excel工作表。这似乎很简单，但我很难过。我只需要添加边界到表格中的所有单元格。

我对VBA不是非常有信心，但一直在寻找这个解决方案几天没有运气。对固定范围提供了很多的帮助，并且设法实现了我所需要的一个柱子，但是我碰到了一堵墙，似乎无法使整个范围工作。

将范围为A1的单元格添加到第1行中的数据最后一列在列A中有数据的行

任何帮助都非常感谢。

Dave

解决方案

也许类似于：

  Sub BoxIt ）
 Set r = Range（A1）。CurrentRegion 
 with r.Borders（xlEdgeLeft）
 .LineStyle = xlContinuous 
 .ColorIndex = 0 
 .TintAndShade = 0 
 .Weight = xlThin 
以
结尾使用r.Borders（xlEdgeTop）
 .LineStyle = xlContinuous 
 .ColorIndex = 0 
 .TintAndShade = 0 
 .Weight = xlThin 
以
结尾使用r.Borders（xlEdgeBottom）
 .LineSty le = xlContinuous 
 .ColorIndex = 0 
 .TintAndShade = 0 
 .Weight = xlThin 
以
结尾使用r.Borders（xlEdgeRight）
 .LineStyle = xlContinuous 
 .ColorIndex = 0 
 .TintAndShade = 0 
 .Weight = xlThin 
用
结束用r.Borders（xlInsideVertical）
 .LineStyle = xlContinuous 
 .ColorIndex = 0 
 .TintAndShade = 0 
 .Weight = xlThin 
以
结尾使用r.Borders（xlInsideHorizo​​ntal）
 .LineStyle = xlContinuous 
 .ColorIndex = 0 
 .TintAndShade = 0 
 .Weight = xlThin 
 End With 
 End Sub 
  

I need to automate the formating of an exported excel worksheet with variable rows and variable columns. It seems so simple but I am stumped. I just need to add borders to all cells in the table.

I'm not terribly confident with VBA but have been searching for a solution to this for a few days with no luck. Plenty of help for fixed ranges, and managed to achieve what I need for a single column but I've hit a wall and can't seem to get the whole range to work.

Example of what I'm trying to do in english:

Add borders to cells in range "A1" to "last column with data in row 1 and last row with data in column A"

Any help is greatly appreciated.

Dave

解决方案

Perhaps something like:

Sub BoxIt()
Set r = Range("A1").CurrentRegion
    With r.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With r.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With r.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With r.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With r.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With r.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

这篇关于Excel宏在变量范围内的边界单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！