Set Named Range with Last Row

[scott]

I'm trying to create a Named Range called "DataRange" based on my values as
listed in LISTING 1. For example, "DataRange" would currently be B2:B10. The
trick to my problem is I will be inserting a row after Row 1 with code, thus
shifting "DataRange" to B3:B11 for example. The other issue is I need the
Named Range starting at B2 to the last row in Column B where data exists in
Column A. To clarify, in LISTING 1 there is no data in B10, but there is
data in A10. I will always need to get the last data row in Column A and
then create the Named Range from B2:B10 for my example numbers below.

I found a code example shown in LISTING 2 below that creates a Named Range,
but it has problems dealing with blanks.

Can someone shed some light on how I can accomplish this?


LISTING 1:

Column A Column B
------------------------------
BOL BOL Billed
196618 196650
196625
196650
196663 196663
196669
196686 196686
196694 196694
196699 196699
196711

LISTING 2:

Sub setNamedRange()

Dim lCol As Long, lRow As Long, rStart As Range
Dim rng As Range, ws As Worksheet

Set ws = ActiveSheet
With ws
Set rStart = .Range("B2")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rng = .Range(rStart, .Cells(lRow, lCol))
.Names.Add Name:="DataRange", RefersTo:=rng
.Range("DataRange").Select
End With
End Sub

 

[Bob Phillips]

Insert>Name>Define...

=OFFSET($B$2,,,COUNT($A:$A),1)

inserting a row ill auto-update it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[scott]

Is there a quick way with code to print the range begin/end cells in a
message box so I can tell if a dynamic named range is where it should be?

I just realized that dynamic named ranges don't show up in EDIT > GO TO.

 

[scott]

I tried using your suggestion, but when I insert a cell in B2 when the range
is B2:B10, the dynamic range changes the $B$2 to $B$3 in your example code.

I tried using the sub AdjustRange() below to handle a new cell, but it just
moves the whole range down, it does include the newly inserted cell in B2.
Any other ideas?

Public Sub AdjustRange()

' this moves a range in sheet 'Passenger' one row down
Dim sht As Worksheet, rng As Range, dest As Range

Set sht = ThisWorkbook.Worksheets("BOL Match")
Set rng = sht.Range("DataRange")
Set dest = rng.Offset(1, 0)

rng.Cut
sht.Paste Destination:=dest

End Sub

 

[Bob Phillips]

Hit F5, and then enter the range name in the edit box.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

I thought that that was wanted you were wanting from your original post.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)