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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Named Range with Last Row
InsertNameDefine...
=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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Named Range with Last Row
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. "Bob Phillips" wrote in message ... InsertNameDefine... =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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Named Range with Last Row
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" wrote in message ... InsertNameDefine... =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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Named Range with Last Row
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) "scott" wrote in message ... 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. "Bob Phillips" wrote in message ... InsertNameDefine... =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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Named Range with Last Row
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) "scott" wrote in message ... 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" wrote in message ... InsertNameDefine... =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" wrote in message ... 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 |
Reply |
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 |