VBA Borders to Last Row Plus 10 Additional Rows

[rsmeyerson]

I'll looking for help with adding borders to all non-empty rows plus 10 additional rows. Here is my code below that already adds borders to all used rows. Can you please help me modify it to add the next 10 rows beneath the last row?

In addition, I need to add medium weight borders to certain columns that I'm currently adding line by line in my code below. Is there a way to do this with multiple columns within the same line of code?

Thanks for your help,

Bob

Sheets("Master").Select
last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = last To 4 Step -1
            Cells(i, "A").Resize(, 77).Borders.LineStyle = xlContinuous
            Cells(i, "G").Borders(xlEdgeLeft).Weight = xlMedium
            Cells(i, "Q").Borders(xlEdgeLeft).Weight = xlMedium
            Cells(i, "AA").Borders(xlEdgeLeft).Weight = xlMedium
    Next i

 

[Scott Huish]

You shouldn't need a loop and that can be reduced to this:

last = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A4:A" & last).Resize(last + 7, 77)
    .Borders.LineStyle = xlContinuous
    Union(.Columns(7), .Columns(17), Columns(27)).Borders(xlEdgeLeft).Weight = xlMedium
End With

 

[rsmeyerson]

Thank you! This works perfectly. Can you please tell me what part of this code adds borders to last row plus 10? I see last + 7 in your code.

 

[rsmeyerson]

I have one last question please. The code you gave me for the left edge medium weight border is working correctly for columns 7 and 17, but for column 37 and any additional columns I add further to the right, the borders are not stopping at the last row. They are continuing indefinitely. Do you see why this might be occurring?

 

[Scott Huish]

This is: last + 7, it's adjusted for the fact that you are not starting on row 1.

Try this instead:

Sub test()
Dim last As Long
last = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A4:A" & last).Resize(last + 7, 77)
    .Borders.LineStyle = xlContinuous
    Intersect(Rows(4).Resize(last + 7), Union(.Columns(7), .Columns(17), Columns(27), Columns(37))).Borders(xlEdgeLeft).Weight = xlMedium
End With
End Sub

 

[rsmeyerson]

Great. Thanks for all your help.