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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

BrianMH

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
Dynamic based on what? The last cell with data in A?
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
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
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Natalia

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

with line -
       With .Borders
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,811
Members
414,104
Latest member
imamalidadashzada

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
Top