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

[Keith]

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

 

[Keith]

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

 

[JMB]

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