在excel中使用VBA宏选取行和放置边框
在excel中使用VBA宏选取行和放置边框
提问于 2014-06-17 19:59:38
我需要在第13行的工作表中找到非空行，并在选中的非空行上放置一个粗框，直到工作表最后使用的行。从C列中，我需要找到非空行。我试过这段代码，但它不工作。你能帮帮我吗？
Sub rowfind3()
Dim cell As Range
Dim r1 As Range
For Each cell In ActiveSheet.Range("C:C")
If (cell.Value <> "") Then
Set r1 = Range("A" & ActiveCell.Row & ":AV" & ActiveCell.Row)
r1.Select
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 = xlMedium
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(xlInsideHorizontal).LineStyle = xlNone
End If
Next cell
End Sub在这段代码中，只显示第一行的边框，但是对于连续的行，边框不会出现。
我还尝试了上面场景的另一个代码，但相同的第一行只显示了边框。
Sub rowfind1()
'
' rowfind Macro
'
'
Dim r1 As Range
Dim lr As Variant
Dim i As Integer
lr = ActiveSheet.UsedRange.Rows.Count
i = 0
For i = 13 To lr - 11
If (Not (IsEmpty(Cells(i, 3).Value))) Then
Set r1 = Range("A" & ActiveCell.Row & ":AV" & ActiveCell.Row)
r1.Select
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 = xlMedium
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(xlInsideHorizontal).LineStyle = xlNone
End If
Next i
End Sub回答 2
Stack Overflow用户
发布于 2014-06-17 20:29:13
首先，不需要使用选择。最终所做的一切可能会使代码变得混乱(如本例所示)。其次，不需要在循环内重新声明范围。这就是循环存在的目的。
下面是它应该是什么样子：
Sub rowfind3()
Dim cell As Range
For Each cell In ActiveSheet.Range("C:C")
If (cell.Value <> "") Then
With cell.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With cell.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With cell.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With cell.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End If
Next cell
End Sub我会考虑更改Activesheet以引用您想要的实际工作表，并只查看UsedRange以提高速度，但该代码现在至少可以让您实现该目标。
基于注释澄清的附录：
lr = ActiveSheet.Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row
With ActiveSheet.Range("C13:C" & lr)
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
.Borders(xlInsideHorizontal).LineStyle = xlNone
End WithStack Overflow用户
发布于 2014-06-17 22:14:17
您是否考虑过使用条件格式？例如，在列上，$A:$AV公式为=$A1<>""，在格式中选择边框。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24263223
复制相关文章
相似问题
腾讯云开发者
