Referencing a named offset address within a range function in VBA Excel

Question

I'm trying to call a range between a fixed cell and a dynamic cell (which i can reference using a defined name and an offset). Im not too sure how the syntax should go. Currently I have:

Range("B21:Range("Anchor").Offset(-1,1)")
'(i know this is incorrect syntax, but just to show you the logic)

Where B21 is the fixed cell. And "Anchor" is the Defined name which has a dynamic cell address.

For some background: I'm trying to call all cells within a column of a table, however the table has an "add rows" function, so the last cell of the column is always changing. My "Anchor" is below the position the last row will be.

Im not very good with VBA language, so please bear with me.

Tim Williams · Accepted Answer · 2019-03-17 23:23:55Z

1

You can do it like this:

Dim ws as worksheet, rng as range

set ws = activesheet

With ws
    Set rng = .Range(.Range("B21"), .Range("Anchor").Offset(-1,1))
end with

answered Mar 17, 2019 at 23:23

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GCode Over a year ago

Thanks! That fixed it!

Collectives™ on Stack Overflow

Referencing a named offset address within a range function in VBA Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related