range in a table form

[Dantz]

i have a named range "second_column" which has a range lets say
D6 to D14
it is in a table form where the D14 bottom border is xlMedium,
my problem is that I want to redimension my named range when the user
creates a new row and have it as the last row in column D(changing its
bottom border to xlMedium) is there a way to find it?

my codes is like this:
' i also don't know how to loop in the column
'covered by "second_column"
For Each cell In Range("second_column") ' how can i know the column
If cell.Borders(xlEdgeBottom).LineStyle = xlContinuous _
And cell.Borders(xlEdgeBottom).Weight = xlMedium Then

Set bottom_cell = cell

End If
Next

Range(Range("second_column"), bottom_cell).Select

ActiveWorkbook.Names.Add Name:="second_column", _
RefersToR1C1:=Selection.Address(, , xlR1C1)

please help me...


thanks in advance

 

[Arvi Laanemets]

Hi

Simply define the named range as dynamic one.

P.e. when range starts from D6 on sheet Sheet1, it has header in cell D5,
there never are any entries in cells D14, and data in range down from D6
always are contignous (no gaps), then define Second_Column like this
=OFFSET(Sheet1!$D$5,1,,COUNTA(Sheet1!$D:$D)-1,1)

The formula you use varies depending on your sheet design, and character fo
data. Instead of COUNTA ypu can use COUNTIF or COUNT functions to determine
the number of rows in range, you may determine the number of rows by entries
in another column, etc., but the main principle remains the same.

Btw., about defining the base point for named range outside of range (in
header row) - it is for case you need to delete some rows of data. When you
define the range as
=OFFSET(Sheet1!$D$6,,,COUNTA(Sheet1!$D:$D)-1,1)
, and later delete the row 6, your named range definition will be corrupted.
The 1st formula in contrary will work - even when you delete all rows from
6th down.

 

[Dantz]

thanks lot...but what if my range down can have a gap?...there maybe a
cell that is empty within the range...

 

[Arvi Laanemets]

Hi

When it's possible, then simply avoid such situations - it makes your life
much easier

When you can't avoid gaps, use an hidden column with enough predefined
formulas, to create an always contignous column, and use this column to
calculate the number of rows in range. P.e. something like this for cell
A100
=IF(AND(OFFSET(A100;1;0)="",B100=""),"",ROW()-NumberOfHeaderRows)