VBA Define Dynamic Name Range

Question

I'm looking at a way in which to name/rename a range based on an adjacent cell value that can change due to sorting/value change, etc. via VBA.

I've tried to outline an example of how the grid is laid out. This is all contained is a pre-defined table. The Yes/No columns contain the condition. If the value is Y then I want the cell to the right of it to be included in the named range.

I've had a look at various dynamic named range queries suggesting the use of OFFSET but I'm struggling to code it in a way that's getting me anywhere.

        Column1 Column2  Column3 Column4

Row1    Y/N1    Balance1 Y/N2    Balance2
Row2    Y       1.000    Y       5.000
Row3    Y       2.000    N       0.000
Row4    N       0.000    N       0.000
Row5    Y       3.000    N       0.000
Row6    Y       4.000    N       0.000

So in your example, you want a named range for column2 (and 4) that would be B2:B3 - or B2:B3;B5:B6? The former is possible with formulas, the latter only with VBA. — Peter Albert
– Peter Albert, Commented Aug 21, 2014 at 6:38
That latter. Based on my above example, the range would be (B2:B3,B5:B6,D2). — jbm
– jbm, Commented Aug 21, 2014 at 16:35

jbm · Accepted Answer · 2014-08-25 14:45:04Z

2

I believe I've figured out the answer. Not sure if it's the most efficient but it appears to be performing as expected.

Sub GetRange()
Dim rngselect As Range, rngfinal As Range, cell As Range
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
Set rngselect = ws.Range("$A:$A,$C:$C")

For Each cell In rngselect
    If cell.Value = "Y" Then
        If rngfinal Is Nothing Then
            Set rngfinal = ws.Range(Cells(cell.Row, cell.Column + 1).Address)
        Else
            Set rngfinal = Application.Union(rngfinal, ws.Range(Cells(cell.Row, cell.Column + 1).Address))
        End If
    End If
Next cell

Names.Add Name:="test", RefersTo:=rngfinal End Sub

answered Aug 25, 2014 at 14:45

jbm

351 silver badge9 bronze badges

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

1 Comment

David Zemens Over a year ago

If it works, it works :) +1 to solve your own question.

Collectives™ on Stack Overflow

VBA Define Dynamic Name Range

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