How to find last row and Add A Border

[unknownymous]

Hello Guys,

I have workbook with Sheets 1 to 10. For each sheet, I need to find the last row, delete the excess vertical lines below it (just CTRL+DELETE). Below the last row, I need to add a vertical border by just copying the border that can be found on A3-AC3 across all sheets. Thanks in advance for all the help.

 

[Yongle]

Try this

VBA
- in the first 10 sheets in the workbook (regardless of name)
- finds last row
- deletes borders below that row
(I need to find the last row, delete the excess vertical lines below it)
- copies all formatting including borders fron A3:AC3 into row below last row
(Below the last row, I need to add a vertical border by just copying the border that can be found on A3-AC3 across all sheets)

Sub AddBorder()
    Application.ScreenUpdating = False:     Application.Calculation = xlCalculationManual
    Dim w As Integer, lr As Long
    For w = 1 To 10
        With Sheets(w)
            .Activate
            lr = .Cells(Rows.Count, 1).End(xlUp).Row
            .Rows(lr & ":" & Rows.Count).Borders.LineStyle = xlLineStyleNone
                With .Range("A3:AC3")
                    .Copy
                    .Offset(lr + 1 - 3).PasteSpecial Paste:=xlPasteFormats
                End With
            Application.Goto Reference:=Range("A" & lr), Scroll:=True
        End With
    Next w
    Application.ScreenUpdating = True:      Application.Calculation = xlCalculationAutomatic
End Sub