Macro to insert all borders on all sheets

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,534
I have written code to insert borders on all sheets from row 9 up to the row before where total appears in Col A (could be total repairs, total expenses etc)


I need the borders to be from Col a to the last Column Containing Text in Row 9.


Your assistance is much appreciated



Code:
 Sub Borders_All_Sheets()

Application.ScreenUpdating = False
Dim lngLstCol As Long, lngLstRow As Long

lngLstRow = ActiveSheet.UsedRange.Rows.Count
lngLstCol = ActiveSheet.UsedRange.Columns.Count

For Each rngCell In Range("A9:A" & lngLstRow)
    If rngCell.Value > "" Then
        r = rngCell.Row
        c = rngCell.Column
        Range(Cells(r, c), Cells(r, lngLstCol)).Select
            With Selection.Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
    End If
Next

Application.ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
not sure why you would want borders outside your used range,

Code:
dim ws as worksheet

for each ws in activeworkbook.worksheets
    ws.Cells.Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
next ws
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,658
Office Version
365, 2010
Platform
Windows, Mobile
I appear to be reading these requests a bit different :confused:

from row 9 up to the row before where total appears in Col A (could be total repairs, total expenses etc
I need the borders to be from Col a to the last Column Containing Text in Row 9
Code:
Sub FindTotal()
    Dim ws As Worksheet, FindTot As Range, myCol As Long, FindRW As Long
    
    For Each ws In ActiveWorkbook.Worksheets
    
        If WorksheetFunction.CountA(ws.Cells) <> 0 Then

            Set FindTot = ws.Columns(1).Find("total*", , xlValues, , xlByRows, xlNext, False)

            If Not FindTot Is Nothing Then
                myCol = ws.Rows(9).Find("*", , xlValues, , xlByColumns, xlPrevious).Column
                FindRW = FindTot.Row - 1
                ws.Range(ws.Cells(9, 1), ws.Cells(FindRW, myCol)).Borders.LineStyle = xlContinuous
            End If
            
        End If
        
    Next
End Sub
Btw, I have interpreted
from row 9 up to the row before where total appears in Col A
to mean
from row 9 down to the row before where total appears in Col A
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,534
Thanks for the help. Your interpretation is 100%


Your code works perfectly
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,752
Members
406,496
Latest member
Arlind Elezi

This Week's Hot Topics

Top