Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Named Range with Last Row
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Compare a selected Range with a Named range and select cells that do not exist | Excel Programming | |||
If any cell in named range = 8 then shade named range | Excel Programming |