Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OFFSET function in named range returning wrong # of rows | Excel Worksheet Functions | |||
Why Copy/Paste fails using Offset & Resize of myRange? | Excel Discussion (Misc queries) | |||
using .resize or an offset | Excel Programming | |||
resize colums / rows in range | Excel Programming | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions |