vba 在excel中使用VBA宏进行行选择和放置边框

Row selection and putting border using VBA macros in excel

提问by Beginnertomacros

I Need to find the non empty rows in a sheet from row 13 and put a top thick border to the selected non empty rows till the last used rows of the sheet. from the column C i need to find the non empty row. I tried this code but it is not working. can u plz help me out

我需要在第 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

In this code only the first row the border is displayed, however for the successive rows the border is not coming.

在此代码中，仅显示第一行边框，但是对于连续行，边框不会出现。

Also i tried another code for the above scenario, but the same first row is only the border is displayed.

此外，我为上述场景尝试了另一个代码，但相同的第一行仅显示边框。

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

回答by bmgh1985

Firstly, no need to use selection. All that ends up doing is potentially confusing the code (as in this case). Secondly, theres no need to re-declare the range inside the loop. Thats what the loop is there for.

首先，不需要使用选择。最终做的所有事情都可能会混淆代码（如本例所示）。其次，无需在循环内重新声明范围。这就是循环的用途。

Here is how it should look:

这是它的外观：

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

I would look at changing the Activesheet to reference the actual sheet you want it on and only look at the UsedRange as well to speed it up a bit, but that code will now at least get you there.

我会考虑更改 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 With

回答by apc

Did you consider to use Conditional Formatting? For example on Columns $A:$AVformula is =$A1<>"", in formatting choose borders.

您是否考虑使用条件格式？例如在列$A:$AV公式是 =$A1<>""，在格式选择边框。