Dynamically set border in excel 2003?

[veek]

i would like to use a border for a series of worksheets i am building in
excel 2003. content in the worksheets will be dynamic so i cannot
pre-determine the range. is there a way to tell excel to build the border
from A1:after-the-last-row-that-has-content-in-column-A"?

i doubt the content will ever extend beyond twnety or so rows but i don't
want to set a static border and then if a worksheet has only 3 rows, the
border doesn't print until after 17 blank rows.

 

[Otto Moehrbach]

Using VBA you can do that. The statement below defines a range of occupied
cells in Column A and 10 columns wide. You would need to add code to create
the borders or maybe even add code to first delete all existing borders
before creating them in a new range. Post back with more detail about the
type of borders you want if VBA is the way you want to go. HTH Otto
Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 10)

 

[veek]

Otto,

Thanks kindly for the reply. I think my biggest problem is being a novice
Excel user with expert intentions

I hope I can explain this - I just wanted to have a simple border outlining
the outside of the range. The range is dynamic. The left and right borders
are known (i.e. there will never be content beyond column F). There will be
no blank rows in the midst of the range so if there is a way to tell excel to
put the bottom border after the last row with content, that would work.
Column A will contain numeric identifiers for each row so although it is
possible that a cell in column B, C, D, E, or F might be empty, column A will
always have content for active rows. SO really what I want to tell excel is
to put the bottom border after the last row that has content in column A.

--

thanks so much!
veek

Using VBA you can do that. The statement below defines a range of occupied
cells in Column A and 10 columns wide. You would need to add code to create
the borders or maybe even add code to first delete all existing borders
before creating them in a new range. Post back with more detail about the
type of borders you want if VBA is the way you want to go. HTH Otto
Set TheRng = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 10)

 

[Otto Moehrbach]

Veek
This little macro first deletes all borders on the sheet, then places an
outline border around the range from A1 down to the last entry in Column A,
7 columns wide. Post back if you need more. HTH Otto

Otto,

Thanks kindly for the reply. I think my biggest problem is being a novice
Excel user with expert intentions

I hope I can explain this - I just wanted to have a simple border
outlining
the outside of the range. The range is dynamic. The left and right
borders
are known (i.e. there will never be content beyond column F). There will
be
no blank rows in the midst of the range so if there is a way to tell excel
to
put the bottom border after the last row with content, that would work.
Column A will contain numeric identifiers for each row so although it is
possible that a cell in column B, C, D, E, or F might be empty, column A
will
always have content for active rows. SO really what I want to tell excel
is
to put the bottom border after the last row that has content in column A.

 

[Otto Moehrbach]

And I forgot to attach the macro. Otto
Sub PlaceBorders()
Dim rRng As Range
Cells.Borders.LineStyle = xlNone
Set rRng = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 7)
rRng.BorderAround LineStyle:=xlContinuous, Weight:=xlThick
End Sub

Otto,

Thanks kindly for the reply. I think my biggest problem is being a novice
Excel user with expert intentions

I hope I can explain this - I just wanted to have a simple border
outlining
the outside of the range. The range is dynamic. The left and right
borders
are known (i.e. there will never be content beyond column F). There will
be
no blank rows in the midst of the range so if there is a way to tell excel
to
put the bottom border after the last row with content, that would work.
Column A will contain numeric identifiers for each row so although it is
possible that a cell in column B, C, D, E, or F might be empty, column A
will
always have content for active rows. SO really what I want to tell excel
is
to put the bottom border after the last row that has content in column A.