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

 

[scott]

Sorry, accidently posted previous message in the wrong forum.