Vba formula loop help

[VbaHell]

Hello all

Can I have some help please

I am trying to work out a more efficient way of achieving this formula below

The range starts at R4:R270 up to IN4:IN270 and it step every 5 and I need to put a border on the Right only
I started to write the code as below but this will take forever and wondered if there is a better way

With Range("R4:R270,W4:W270,AB4:AB270,AG4:AG270,AL4:AL270,AQ4:AQ270,AV4:AV270").Borders(xlRight)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

<!--StartFragment--> <colgroup><col width="65" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

 

[p45cal]

For colm = 18 To 248 Step 5
    With Cells(4, colm).Resize(267).Borders(xlRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
Next colm

 

[hiker95]

VbaHell,

By adding the two lines of code in BOLD, the macro execute time went from 0.078 seconds to 0.016 seconds, on my Lenovo T61 laptop computer.

Sub Test()
' hiker95, 10/28/2012
' http://www.mrexcel.com/forum/excel-questions/666568-visual-basic-applications-formula-loop-help.html

[B][SIZE=3]Application.ScreenUpdating = False[/SIZE][/B]

With Range("R4:R270,W4:W270,AB4:AB270,AG4:AG270,AL4:AL270,AQ4:AQ270,AV4:AV270").Borders(xlRight)
  .LineStyle = xlContinuous
  .Weight = xlThin
  .ColorIndex = xlAutomatic
End With

[B][SIZE=3]Application.ScreenUpdating = True[/SIZE][/B]

End Sub

 

[VbaHell]

P45Cal

Your a genius
So simple yet far beyond my grasp

 

[VbaHell]

Hiker95
Thank you pointing this out to me, it certainly speeds things up

 

[hiker95]

VbaHell,

Thanks for the feedback.

You are very welcome. Glad we could help.

Come back anytime.

 

[VbaHell]

P45Cal

This steps every 12 but the border is across instead of down
Can you please help me out with the same type of code as the previous post

With Range("A7:IS7,A19:IS19,A31:IS31,A43:IS43,A55:IS55,A67:IS67,A79:IS79,A91:IS91,A103:IS103,A115:IS115,A127:IS127,A139:IS139,A151:IS151,A163:IS163,A175:IS175,A187:IS187,A199:IS199,A211:IS211,A223:IS223,A235:IS235,A247,IS247,A259,IS259,A271:IS271").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

 

[p45cal]

For rw = 7 To 271 Step 12
    With Cells(rw, "A").Resize(, 253).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
Next rw

 

[VbaHell]

P45cal

I am trying to learn VBA so can I break this down and tell you how I think this works


For rw = 7 To 271 Step 12 The first Row is Row 7 and the last row is row 271 starting from row 7 move down every 12 rows
With Cells(rw, "A").Resize(, 253).Borders(xlEdgeTop) Starting with (rw = Row 7) but in column "A" Does the .Resize (, 253) tell you how many columns</pre></pre>

 

[p45cal]

Yes, the loop control variable rw changes its value by adding 12 for every iteration of the loop. Inside the loop rw can be used (or not) any way you like. In this case I use it to define the row number on the sheet.
Cells(rw,"A") is the cell A7, then A19 etc.
The resize bit, well, your best bet is to use the vba Help, just put your editing cursor on the word resize and press F1. There it says:
Resizes the specified range. Returns a Range object that represents the resized range.
expression.Resize(RowSize, ColumnSize)

We've used it as follows: For the expression bit we've used Cells(rw,"A") - a single cell. The bit in brackets (,253) has nothing before the comma, that'd be the rowSize argument, which means don't change it from what's in expression. The ColumnSize bit however is 253, so resize that single cell, the same number of rows as it already is (1 row) and 253 columns wide. This is your columns A:IS.
VBA Help is your friend