Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting area of unfixed size, creating borders
Hello. I am kinda new to Excel macros and I am trying to add th finishing touches to a macro I've designed that produces a report tha contains a simple four column table in the middle of the page. I wan the macro to select this table, put borders around it, and add bolde sums to some of the columns. The hard part is each time the macro i run, the table length is not the same, meaning it could have 5 rows fo one report or it could have as many as 50 rows. The top of the table i always in the same location, so I think I can maneuver with some vba t select the area but I need your help. I tried selecting the top left of the table (cell B13) and then using ActiveCell.CurrentRegion.Select and it almost works, but for som reason, it also selects the cells directly above my table's headers which I don't want included. Here's an example of the simple table. Once the macro has finishe running, the user will be entering numbers into the blank fields, som of which contain formulas. I want the macro to put a simple borde around all the cells and summarize columns C and D, bold those total and add borders around them too. ______ColB ColC ColD Row14 Value1 blank blank Row15 Value2 blank blank Row16 Value3 blank blank Thanks for your help! -- paulol ----------------------------------------------------------------------- paulold's Profile: http://www.excelforum.com/member.php...fo&userid=3337 View this thread: http://www.excelforum.com/showthread.php?threadid=53207 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting area of unfixed size, creating borders
This is not the total answer, but I expect you're going to need a
routine to find the bottom row.. Sub LastRowFind() Worksheets(1).Unprotect ActiveSheet.Range("a16384").Select Selection.End(xlUp).Select endrow = ActiveCell.Row() Worksheets("scratch").Range("C6").Value = endrow Worksheets(1).Protect End Sub You can then use the variable "endrow" to establish bottom for border. paulold wrote: Hello. I am kinda new to Excel macros and I am trying to add the finishing touches to a macro I've designed that produces a report that contains a simple four column table in the middle of the page. I want the macro to select this table, put borders around it, and add bolded sums to some of the columns. The hard part is each time the macro is run, the table length is not the same, meaning it could have 5 rows for one report or it could have as many as 50 rows. The top of the table is always in the same location, so I think I can maneuver with some vba to select the area but I need your help. I tried selecting the top left of the table (cell B13) and then using ActiveCell.CurrentRegion.Select and it almost works, but for some reason, it also selects the cells directly above my table's headers, which I don't want included. Here's an example of the simple table. Once the macro has finished running, the user will be entering numbers into the blank fields, some of which contain formulas. I want the macro to put a simple border around all the cells and summarize columns C and D, bold those totals and add borders around them too. ______ColB ColC ColD Row14 Value1 blank blank Row15 Value2 blank blank Row16 Value3 blank blank Thanks for your help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting area of unfixed size, creating borders
Hi, Im pretty new to excel macros as well, and Im not sure I understoo exactly what you need, but you could try with Range(Selection, Selection.End(xlDown)).Select Its the same as using CTRL+SHIFT+DOWNARROW - which means if you have list of unknown length, it will just select from where you are, to th last filled row going down. The only thing is that your active cell need to be in the filled colum -- lonfnic ----------------------------------------------------------------------- lonfnico's Profile: http://www.excelforum.com/member.php...fo&userid=3339 View this thread: http://www.excelforum.com/showthread.php?threadid=53207 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error: "the copy area & the paste area are not the same size & sh | Excel Discussion (Misc queries) | |||
How to get print area borders - UsedRange not correct. | Excel Programming | |||
Creating cells with Diagonal borders | Excel Discussion (Misc queries) | |||
Creating Borders with a formula | Excel Programming | |||
selecting used area | Excel Programming |