Newbie with a desgin question

[Bruce A. Julseth]

I working in an area on a spreadsheet that can have a variable number of
rows. The user can add and subtract rows, as needed. Now, I m developing a
function that will "walk" down through these rows each time some data is
changed. In the "static" number of rows, it's fairly easy. I just walk down
through the rows, top to bottom, because I know exactly where the first row
is and where the last row is.

Now in the variable row case (real world) I don't know. I using a "Named"
column and that will work except where the user adds a row at the very
beginning or at the very end. The "Named" column doesn't pick up the new
rows when they are added at the very beginning or the very end.

So, I have changed the "Named" column to include the row immediately before
and immediately after. Then I am safe. However, I still only want to search
the rows of interest, so I am trying to find some way to adjust the size of
the named column by changeing the beginning row to the next row(A3 -> A4)
and the end row up one (A304 -> A303). If this is what I have to do, how do
I do it?

Or, perhaps you guys and gals can suggest a better way.

Thanks very much for your help..

 

[Barb Reinhardt]

One option is to use a dynamic named range defined using the OFFSET function.

YOu'll want something like this

=OFFSET(A3, 1, 0, COUNTA(A:A), 1)

A3 is the reference
1 row down from A3
0 columns to left or right of A3,
Resize the range to be count of the non-empty cells in A. YOu may need to
adjust this a bit.
1 column wide

Adjust to suit.

 

[Bruce A. Julseth]

Since I don't know what I'm talking about, maybe the following is not true.

I don't think "Resize the range to be count of the non-empty cells in A"
will work because Rows [A3:A240] {240 Can Change) are the rows I am
interested in. UNDER A240, is some other data that I don't want my function
to reach. I HAVE TO stop at 240. That is why I thought giving my A column a
name, one row above and one row below would solve the problem. To me it
sounds a bit cluggy. I was wondering if there was a better way.

Now that I have given you a bit more information, what do you think.

Thanks for the response...

 

[Rick Rothstein]

If I understand you correctly, you want to take your named range and
reference the all the cells except the first and last one. If that is
correct, you can reference those cells using something like this...

Set InnerRange = Range("Named").Offset(1).Resize(Range("Named").Count - 2)

--
Rick (MVP - Excel)

Since I don't know what I'm talking about, maybe the following is not
true.

I don't think "Resize the range to be count of the non-empty cells in A"
will work because Rows [A3:A240] {240 Can Change) are the rows I am
interested in. UNDER A240, is some other data that I don't want my
function to reach. I HAVE TO stop at 240. That is why I thought giving my
A column a name, one row above and one row below would solve the problem.
To me it sounds a bit cluggy. I was wondering if there was a better way.

Now that I have given you a bit more information, what do you think.

Thanks for the response...

One option is to use a dynamic named range defined using the OFFSET
function.

YOu'll want something like this

=OFFSET(A3, 1, 0, COUNTA(A:A), 1)

A3 is the reference
1 row down from A3
0 columns to left or right of A3,
Resize the range to be count of the non-empty cells in A. YOu may need
to
adjust this a bit.
1 column wide

Adjust to suit.

 

[Barb Reinhardt]

OK, then I have some other questions:

1) Does the data always start in A3? If not how do you know where it
starts?
2) Is there data in every row after A3 until you get to A240 (or wherever
it ends)?
3) How do you know when the data ends? Is there an empty cell in the next
row?

Barb Reinhardt

 

[Bruce A. Julseth]

Yes, that is correct. I want to reference all the rows in my named range
except the first and last.

Your suggestion "Looks" to me like it would work. You're taking my named
range, Offset"ing"
by 1 row and then resizing my name range by deleting 2 rows.

I see you use "Set." When do I have to use set. I thought that went away
with VB 3 or 4!!

Thanks for the response.

If I understand you correctly, you want to take your named range and
reference the all the cells except the first and last one. If that is
correct, you can reference those cells using something like this...

Set InnerRange = Range("Named").Offset(1).Resize(Range("Named").Count - 2)

--
Rick (MVP - Excel)

Since I don't know what I'm talking about, maybe the following is not
true.

I don't think "Resize the range to be count of the non-empty cells in A"
will work because Rows [A3:A240] {240 Can Change) are the rows I am
interested in. UNDER A240, is some other data that I don't want my
function to reach. I HAVE TO stop at 240. That is why I thought giving
my A column a name, one row above and one row below would solve the
problem. To me it sounds a bit cluggy. I was wondering if there was a
better way.

Now that I have given you a bit more information, what do you think.

Thanks for the response...

One option is to use a dynamic named range defined using the OFFSET
function.

YOu'll want something like this

=OFFSET(A3, 1, 0, COUNTA(A:A), 1)

A3 is the reference
1 row down from A3
0 columns to left or right of A3,
Resize the range to be count of the non-empty cells in A. YOu may
need to
adjust this a bit.
1 column wide

Adjust to suit.

--
HTH,

Barb Reinhardt



:

I working in an area on a spreadsheet that can have a variable number
of
rows. The user can add and subtract rows, as needed. Now, I m
developing a
function that will "walk" down through these rows each time some data
is
changed. In the "static" number of rows, it's fairly easy. I just walk
down
through the rows, top to bottom, because I know exactly where the first
row
is and where the last row is.

Now in the variable row case (real world) I don't know. I using a
"Named"
column and that will work except where the user adds a row at the very
beginning or at the very end. The "Named" column doesn't pick up the
new
rows when they are added at the very beginning or the very end.

So, I have changed the "Named" column to include the row immediately
before
and immediately after. Then I am safe. However, I still only want to
search
the rows of interest, so I am trying to find some way to adjust the
size of
the named column by changeing the beginning row to the next row(A3 ->
A4)
and the end row up one (A304 -> A303). If this is what I have to do,
how do
I do it?

Or, perhaps you guys and gals can suggest a better way.

Thanks very much for your help..


.

 

[Bruce A. Julseth]

1) In general it starts in A3, but I can't be sure. That is why my named
range starts one row above the rows of interest.

2) There is none useful data between my start and end rows, but I am able to
avoid it in my row search

3) I don't know where it ends. That is why I am using a named range that is
one row longer then what I need. That way,
no matter where my customer enters or deletes rows, the name range will
automatically adjust for these changes (I think!!)

I think Rick Rothstein's suggestion will work. I'll give it a try a little
later today..

Thanks for the response.

OK, then I have some other questions:

1) Does the data always start in A3? If not how do you know where it
starts?
2) Is there data in every row after A3 until you get to A240 (or
wherever
it ends)?
3) How do you know when the data ends? Is there an empty cell in the
next
row?

Barb Reinhardt

Since I don't know what I'm talking about, maybe the following is not
true.

I don't think "Resize the range to be count of the non-empty cells in A"
will work because Rows [A3:A240] {240 Can Change) are the rows I am
interested in. UNDER A240, is some other data that I don't want my
function
to reach. I HAVE TO stop at 240. That is why I thought giving my A
column a
name, one row above and one row below would solve the problem. To me it
sounds a bit cluggy. I was wondering if there was a better way.

Now that I have given you a bit more information, what do you think.

Thanks for the response...

 

[Rick Rothstein]

I used the Set example because you didn't tell us how wanted to reference
the reduced range. Set is used to set a reference to an object (in VBA,
Range is an object). So, in my example, you do this first...

Set InnerRange = Range("Named").Offset(1).Resize(Range("Named").Count - 2)

and then in the rest of the procedure, you could simply use InnerRange (or
any name you choose to call this object) to reference that range, for
example...

MsgBox InnerRange.Address

Another choice is to use a With/End With block instead of Set'ting the
reference. For example...

With Range("Named").Offset(1).Resize(Range("Named").Count - 2)
MsgBox .Address
End With

What approach you use is up to you; but, again, you didn't tell us how you
needed to use the reduced range, so I hade to make a guess.

Oh, and if you declare your variables (which I think should always be done),
you would Dim the InnerRange variable as Range.

--
Rick (MVP - Excel)

Yes, that is correct. I want to reference all the rows in my named range
except the first and last.

Your suggestion "Looks" to me like it would work. You're taking my named
range, Offset"ing"
by 1 row and then resizing my name range by deleting 2 rows.

I see you use "Set." When do I have to use set. I thought that went away
with VB 3 or 4!!

Thanks for the response.

If I understand you correctly, you want to take your named range and
reference the all the cells except the first and last one. If that is
correct, you can reference those cells using something like this...

Set InnerRange = Range("Named").Offset(1).Resize(Range("Named").Count -
2)

--
Rick (MVP - Excel)

Since I don't know what I'm talking about, maybe the following is not
true.

I don't think "Resize the range to be count of the non-empty cells in A"
will work because Rows [A3:A240] {240 Can Change) are the rows I am
interested in. UNDER A240, is some other data that I don't want my
function to reach. I HAVE TO stop at 240. That is why I thought giving
my A column a name, one row above and one row below would solve the
problem. To me it sounds a bit cluggy. I was wondering if there was a
better way.

Now that I have given you a bit more information, what do you think.

Thanks for the response...

message One option is to use a dynamic named range defined using the OFFSET
function.

YOu'll want something like this

=OFFSET(A3, 1, 0, COUNTA(A:A), 1)

A3 is the reference
1 row down from A3
0 columns to left or right of A3,
Resize the range to be count of the non-empty cells in A. YOu may
need to
adjust this a bit.
1 column wide

Adjust to suit.

--
HTH,

Barb Reinhardt



:

I working in an area on a spreadsheet that can have a variable number
of
rows. The user can add and subtract rows, as needed. Now, I m
developing a
function that will "walk" down through these rows each time some data
is
changed. In the "static" number of rows, it's fairly easy. I just walk
down
through the rows, top to bottom, because I know exactly where the
first row
is and where the last row is.

Now in the variable row case (real world) I don't know. I using a
"Named"
column and that will work except where the user adds a row at the very
beginning or at the very end. The "Named" column doesn't pick up the
new
rows when they are added at the very beginning or the very end.

So, I have changed the "Named" column to include the row immediately
before
and immediately after. Then I am safe. However, I still only want to
search
the rows of interest, so I am trying to find some way to adjust the
size of
the named column by changeing the beginning row to the next row(A3 ->
A4)
and the end row up one (A304 -> A303). If this is what I have to do,
how do
I do it?

Or, perhaps you guys and gals can suggest a better way.

Thanks very much for your help..


.

 

[Bruce A. Julseth]

I am using my range in a For Each loop

Dim Cell as Range
Dim InnerRange as Range

For Each Cell in InnerRange

next Cell

And, I aways Dim my variables. I even have "manditory" dim define turned on.

So, I am using it as an object.

Where would I use "Range" when it is NOT an object.

Thanks for the response

I used the Set example because you didn't tell us how wanted to reference
the reduced range. Set is used to set a reference to an object (in VBA,
Range is an object). So, in my example, you do this first...

Set InnerRange = Range("Named").Offset(1).Resize(Range("Named").Count - 2)

and then in the rest of the procedure, you could simply use InnerRange (or
any name you choose to call this object) to reference that range, for
example...

MsgBox InnerRange.Address

Another choice is to use a With/End With block instead of Set'ting the
reference. For example...

With Range("Named").Offset(1).Resize(Range("Named").Count - 2)
MsgBox .Address
End With

What approach you use is up to you; but, again, you didn't tell us how you
needed to use the reduced range, so I hade to make a guess.

Oh, and if you declare your variables (which I think should always be
done), you would Dim the InnerRange variable as Range.

--
Rick (MVP - Excel)

Yes, that is correct. I want to reference all the rows in my named range
except the first and last.

Your suggestion "Looks" to me like it would work. You're taking my named
range, Offset"ing"
by 1 row and then resizing my name range by deleting 2 rows.

I see you use "Set." When do I have to use set. I thought that went away
with VB 3 or 4!!

Thanks for the response.

If I understand you correctly, you want to take your named range and
reference the all the cells except the first and last one. If that is
correct, you can reference those cells using something like this...

Set InnerRange = Range("Named").Offset(1).Resize(Range("Named").Count -
2)

--
Rick (MVP - Excel)


Since I don't know what I'm talking about, maybe the following is not
true.

I don't think "Resize the range to be count of the non-empty cells in
A" will work because Rows [A3:A240] {240 Can Change) are the rows I am
interested in. UNDER A240, is some other data that I don't want my
function to reach. I HAVE TO stop at 240. That is why I thought giving
my A column a name, one row above and one row below would solve the
problem. To me it sounds a bit cluggy. I was wondering if there was a
better way.

Now that I have given you a bit more information, what do you think.

Thanks for the response...

message One option is to use a dynamic named range defined using the OFFSET
function.

YOu'll want something like this

=OFFSET(A3, 1, 0, COUNTA(A:A), 1)

A3 is the reference
1 row down from A3
0 columns to left or right of A3,
Resize the range to be count of the non-empty cells in A. YOu may
need to
adjust this a bit.
1 column wide

Adjust to suit.

--
HTH,

Barb Reinhardt



:

I working in an area on a spreadsheet that can have a variable number
of
rows. The user can add and subtract rows, as needed. Now, I m
developing a
function that will "walk" down through these rows each time some data
is
changed. In the "static" number of rows, it's fairly easy. I just
walk down
through the rows, top to bottom, because I know exactly where the
first row
is and where the last row is.

Now in the variable row case (real world) I don't know. I using a
"Named"
column and that will work except where the user adds a row at the
very
beginning or at the very end. The "Named" column doesn't pick up the
new
rows when they are added at the very beginning or the very end.

So, I have changed the "Named" column to include the row immediately
before
and immediately after. Then I am safe. However, I still only want to
search
the rows of interest, so I am trying to find some way to adjust the
size of
the named column by changeing the beginning row to the next row(A3 ->
A4)
and the end row up one (A304 -> A303). If this is what I have to do,
how do
I do it?

Or, perhaps you guys and gals can suggest a better way.

Thanks very much for your help..


.

 

[Wen]

Assume your named "column" is named "theColumn". Try this:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim R As Range
Set R = Range("theColumn").CurrentRegion.Columns("a")
ThisWorkbook.Names.Add Name:="theColumn", RefersTo:=R
End Sub
This should make "theColumn" range include the newly added cells.
HTH.