VBA Border

[slivesay]

I have the below coding and it works great, but I'd like add on to it. Right now it puts a border around all cells that contain value. My number of columns will always be the same (A-Y), but my rows will be different. How can I add the border to the blank cells right after my last column that contains data?


'BORDERS
With ActiveSheet.UsedRange.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

 

[Fluff]

How about

With ActiveSheet.UsedRange
   With .Resize(, .Columns.Count + [COLOR=#ff0000]1[/COLOR]).Borders
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = xlAutomatic
   End With
End With

change the value in red if you want more than 1 extra column

 

[DataBlake]

you could try using row count on the longest column or whatever column you want.

so maybe

Sub test()
Dim lastRow As Long
Dim rng As Range

lastRow = Range("Y" & Rows.Count).End(xlUp).row
Set rng = Range("A1:Y" & lastRow)

rng.Select

With ActiveSheet.UsedRange.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub

note: there cant be blank cells in Y

edit: Fluff strikes again- use theirs

 

[DataBlake]

ah i didnt comprehend the +1 column

Sub test()
Dim lastRow As Long
Dim rng As Range

lastRow = Range("Y" & Rows.Count).End(xlUp).row
Set rng = Range("A1:Z" & lastRow)

rng.Select

With ActiveSheet.UsedRange.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub

 

[Fluff]

@BlakeSkate.
The first part of you code is doing absolutely nothing
You would need to change the code to look at the selection rather than the usedrange

 

[DataBlake]

@BlakeSkate.
The first part of you code is doing absolutely nothing
You would need to change the code to look at the selection rather than the usedrange

right...... well........i'll do a fixit later today
gotta stay true to my signature somehow

 

[slivesay]

Thank you all so much!!! I used the code w/ the +1 and it works great!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[slivesay]

I may need to do a new thread, but may I ask ---

Would the coding be hard if... In this same template to find the last row (since they vary) and total the numbers in one column? The 1st row is my headers, so J1 will be a word and the rest of the column will be numbers. Can I total the numbers and put the answer in the cell after the last row?

 

[Fluff]

Yes that can be done, but does need a new thread.