VBA Border

slivesay

New Member
Joined
Jan 4, 2019
Messages
44
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
Windows
How about
Code:
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

Well-known Member
Joined
Jan 26, 2015
Messages
717
Office Version
2016
Platform
Windows
you could try using row count on the longest column or whatever column you want.

so maybe
Code:
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
 
Last edited:

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
717
Office Version
2016
Platform
Windows
ah i didnt comprehend the +1 column

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
Windows
@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

Well-known Member
Joined
Jan 26, 2015
Messages
717
Office Version
2016
Platform
Windows
@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 :LOL:
gotta stay true to my signature somehow
 
Last edited:

slivesay

New Member
Joined
Jan 4, 2019
Messages
44
Thank you all so much!!! I used the code w/ the +1 and it works great! :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

slivesay

New Member
Joined
Jan 4, 2019
Messages
44
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
Windows
Yes that can be done, but does need a new thread.
 

Forum statistics

Threads
1,086,123
Messages
5,387,978
Members
402,092
Latest member
S_S

Some videos you may like

This Week's Hot Topics

Top