Selecting area of unfixed size, creating borders

[paulold]

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!

[email protected]

[windsurferLA]

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!
>
> [email protected]
>
>

[lonfnico]

Hi,

Im pretty new to excel macros as well, and Im not sure I understood 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 a list of unknown length, it will just select from where you are, to the last filled row going down.

The only thing is that your active cell need to be in the filled column