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

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!

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