Borders

[Scott J]

Hi everyone,

I was wondering if anyone can help me with some code to dynamically place a
border on the last row of a spreadsheet. The issue for me is that the
spreadsheet varies in length (based on filtering), but the columns are static
(A:S). What I was hoping to do is place a border on the last row so the
spreadsheet will be completly framed out once printed.

Does anyone have any ideas?

Thanks,

SJ

 

[JLGWhiz]

If all the columns are the same length as Column A then

Sub brdrArnd()
lr = Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Range("A1:S" & lr).BorderAround Weight:=xlMedium
End Sub

However, If you save the file with the border, then when you run the
macro again, if the column length has changed, you will have another
frame for each change in length. You will have to remove the border
before saving to prevent this anomaly.

 

[JLGWhiz]

What you could do is put the following line of code after your print
activity is completed.

ActiveSheet.Range("A1:S" & lr).Borders.LineStyle = xlLineStyleNone

That will remove the border so you can do other things and then save the file.
If you plan on the column lengths changing and the file being printed during
a single session, then I suggest you put the border commands in separate subs
and use a conditional If ... Then statement to call them as needed. The
border will not automatically move with a change in the size of the database,
but the macro will resize the border each time it is run, based on the size
of the database. Now that I have you thoroughly confused, I'll say good
night.

 

[Robert McCurdy]

The problem with this (no time to work it out now), is how you'd want the previous border dealt with. Maybe assign the range to a range name?

Sub BorderLastRow()
'get rid of the previous border?
ActiveSheet.UsedRange.BorderAround LineStyle = xlLineStyleNone
Dim Rw As Long, bRng As Range
Rw = Cells(Rows.Count, 1).End(xlUp).Row
Set bRng = Range("A1:S" & Rw)
bRng.BorderAround Weight:=xlMedium
End Sub


Regards
Robert McCurdy
Hi everyone,

I was wondering if anyone can help me with some code to dynamically place a
border on the last row of a spreadsheet. The issue for me is that the
spreadsheet varies in length (based on filtering), but the columns are static
(A:S). What I was hoping to do is place a border on the last row so the
spreadsheet will be completly framed out once printed.

Does anyone have any ideas?

Thanks,

SJ

 

[Scott J]

Thanks for the response. This code works well!! I realized after I posted
that I forgot to mention that I would like to have each page have a border
around the entire spreadsheet. So when you print, or hit print preview, no
matter how many rows there are, a border is placed around the entire
spreadseet at every page break.

I hope this makes sense.

Thanks

 

[Scott J]

Robert,

Thanks for your reply. This code works great, and you are correct about
having to turn it off when done printing. Actually, after I posted I
realized I didn't mention that I would really like to have a border around
the entire worksheet at each page break. So, when you print the sheet, the
data on each page has a border around it. The issue is with the last page,
since the amount of data that shows up on that page will vary, so the border
would have to vary.

So, how do I get a macro to place full border around each full page of data
and then a varying page of data?

Thanks again!!

Scott J

 

[JLGWhiz]

I think the only way to do that is to print with gridlines visible. You can
set that option in PageSetup.

 

[Scott J]

I was affraid that might be the answer. I just wasn't sure if there was some
kind of pagebreak command or something like that to utilize.