Set new range based on rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
 

I have a named range in a worksheet (auto-expanding named range based on
Stephen Bullen's funchart)

I use VBA application.Match to identify if new values are part of that
range. If so, I modify row contents, and if not, I need to add the new value
to the end of the range (and then modify row contents in that new row)

I found the code:
= rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Value
in a google search of posts, but have been unsuccessful in adapting it to my
needs. What I'd like to do, rather than pulling the value, is set a brand
new range ("TempRange") to this single cell so I can use it in several parts
of my code- to get the row number, to paste a new value, and to do some
calculations.

I've tried syntax like:

Set TempRow = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
or
=rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Range

As a workaround I even tried to get the relevant properties directly
TempRow = rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Row

but no luck.

Can anyone tell me the proper way to refer to this single celled range, or
at least pull cell (range) properties from it?

Many thanks,
Keith

Set new range based on rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
 

I think I found my problem- I had replaced the auto-expanding named range
with a fixed range, which happened to be 64000 rows- so Excel can't offset
the range by that number of rows. I'll play some more and post again in this
thread if I can't get it working.
Thanks,
Keith

"Keith" wrote in message
...
I have a named range in a worksheet (auto-expanding named range based on
Stephen Bullen's funchart)

I use VBA application.Match to identify if new values are part of that
range. If so, I modify row contents, and if not, I need to add the new
value to the end of the range (and then modify row contents in that new
row)

I found the code:
= rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Value
in a google search of posts, but have been unsuccessful in adapting it to
my needs. What I'd like to do, rather than pulling the value, is set a
brand new range ("TempRange") to this single cell so I can use it in
several parts of my code- to get the row number, to paste a new value, and
to do some calculations.

I've tried syntax like:

Set TempRow = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
or
=rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Range

As a workaround I even tried to get the relevant properties directly
TempRow = rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Row

but no luck.

Can anyone tell me the proper way to refer to this single celled range, or
at least pull cell (range) properties from it?

Many thanks,
Keith

Set new range based on rng.Offset(rng.Rows.Count, 0).Resize(1,
 

Maybe something like

Sub test()
Set rng = Range("A1:B45000")
MsgBox rng.Rows(1).Offset(rng.Rows.Count, 0).Address
End Sub

Or
Sub test()
Set rng = Range("A1:B45000")
MsgBox rng.Cells(1, 1).Offset(rng.Rows.Count, 0).Address
End Sub



"Keith" wrote:

I think I found my problem- I had replaced the auto-expanding named range
with a fixed range, which happened to be 64000 rows- so Excel can't offset
the range by that number of rows. I'll play some more and post again in this
thread if I can't get it working.
Thanks,
Keith

"Keith" wrote in message
...
I have a named range in a worksheet (auto-expanding named range based on
Stephen Bullen's funchart)

I use VBA application.Match to identify if new values are part of that
range. If so, I modify row contents, and if not, I need to add the new
value to the end of the range (and then modify row contents in that new
row)

I found the code:
= rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Value
in a google search of posts, but have been unsuccessful in adapting it to
my needs. What I'd like to do, rather than pulling the value, is set a
brand new range ("TempRange") to this single cell so I can use it in
several parts of my code- to get the row number, to paste a new value, and
to do some calculations.

I've tried syntax like:

Set TempRow = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
or
=rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Range

As a workaround I even tried to get the relevant properties directly
TempRow = rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Row

but no luck.

Can anyone tell me the proper way to refer to this single celled range, or
at least pull cell (range) properties from it?

Many thanks,
Keith