VBA- Inside bordering

Natalia

Board Regular
Joined
Feb 20, 2009
Messages
72
Hi,

I have recorded a macro for inside bordering, how can this be cleaned and have a dynamic range A6:E

Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A6:E9").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    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 = xlThin
    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
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Natalia

Revised macro :-
Code:
Sub Macro1()
'
' Macro1 Macro
'

'
Dim cell As Range, Ctxt As String, LRe As Long
' Find the last row in column E
LRe = Range("E" & Rows.Count).End(xlUp).Row ' Last row for Column E going up
LRe = Range("E6").End(xlDown).Row                ' Last row for Column E going down
With Range("A6:E" & LRe)
     .Borders(xlDiagonalDown).LineStyle = xlNone
     .Borders(xlDiagonalUp).LineStyle = xlNone
     .Borders
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .Weight = xlThin
    End With
End With
End Sub

Checking for the last row in Column E (LRe) :-
Going down it find the last occupied cell before a blank cell ie cells E6 to E9 will have data.
Going up it will find the last occupied cell on the sheet.

Change the cell reference if required.

Remove whichever LRe line you don't want.
Both the "Diagonal" lines can be removed if you know that diagonal lines won't have been drawn previously.

NB Nothing is selected. The actions can take place without having to "Select".

hth
 
Upvote 0
Natalia

Apologies, correction to code :--
Code:
replace line - 
       .Borders

with line -
       With .Borders
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top