Repeating Macro

NotSoGoodWithMacros

New Member
Joined
Jul 7, 2008
Messages
4
Hi

I have the below macro that I need to have it repeat until the end of the data, I'm not brilliant with how macro's actually work, so could you help me figure out what I need to place in the macro & where, to get it to work.

Range("A2:Q5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Something like this perhaps.
Code:
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
With Range("A2:Q" & LastRow)
    With .Borders
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
End With
 
Upvote 0
Hello

thanks for the replies, but where would I copy & paste this data in the macros visual basic editor. I really don't know what I'm doing, hence all the very silly questions.

Please note I want to repeat the border patterning in the macro (data) to the end of the data fields (A1 to Q3546).

Thank you
 
Upvote 0
You'll want to paste that code in a module.
Open the VBA editor, and click insert at the top of the screen. Then click Module. A window should appear at right where you can paste the code. To execute the code, go to the Tools menu in Excel, go to macros, then choose the one you want to run.

Now I'm not quite clear what you're asking. What exactly is it that you want to repeat?
Do you have multiple discreet patches of data that you want borders around, or do you want borders around each cell, or just one giant border across the entire range A1:Q3546?

Regards,
Mike
 
Last edited:
Upvote 0
Hi Mike


Thanks for your reply.

I have multiple data that needs a border around every four line (e.g. A2-Q5). I need the border to repeat every four lines until the end of the data (=A1-Q3546).

Regards
:confused:
 
Upvote 0
The more advanced coders around may scold me for suggesting a loop, but this should achieve the effect I think you want.

Code:
Sub borders()

Dim CR As Long
CR = 1
Do While CR < 3456
    With Range("A" & CR & ":Q" & CR + 4)
        With .borders
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        .borders(xlInsideVertical).LineStyle = xlNone
        .borders(xlInsideHorizontal).LineStyle = xlNone
    End With
CR = CR + 4
Loop
End Sub


I used Norie's code and simply added in the loop.
You can play with the range and the value of CR to fit your needs. Is that what you're looking for?
 
Upvote 0
MRA

I suggest you don't call the sub borders.:eek:

PS There's nothing wrong with loops if they get the job done.:)
 
Upvote 0
Hehe cool. Just seems like I see better coders trying to avoid loops like the plague :)
 
Upvote 0
Dear MRA

This worked like a charm, you've save me loads of boring hours place borders around my spreadsheets.

Thank you all for your help.

NotSoGoodWithMacros.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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