Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Borders
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Borders
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. "Scott J" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Borders
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. "Scott J" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Borders
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 "Scott J" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Borders
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 "JLGWhiz" wrote: 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. "Scott J" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Borders
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 "Robert McCurdy" wrote: 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 "Scott J" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Borders
I think the only way to do that is to print with gridlines visible. You can
set that option in PageSetup. "Scott J" wrote: 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 "Robert McCurdy" wrote: 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 "Scott J" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Borders
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. "JLGWhiz" wrote: I think the only way to do that is to print with gridlines visible. You can set that option in PageSetup. "Scott J" wrote: 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 "Robert McCurdy" wrote: 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 "Scott J" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Borders | Excel Discussion (Misc queries) | |||
borders | Excel Discussion (Misc queries) | |||
Borders | Excel Discussion (Misc queries) | |||
Borders | Excel Programming | |||
Help with borders | Excel Programming |