Named Range
in [Programming]
|
From: Munchkin on 24 Mar 2010 09:36 I'm creating a template in which a user can copy & paste their records and format it a certain way w/a macro button. One portion of the macro should create a named range for all records that appear in column J, starting at J4. However, as you can see from my code the marco is always going to name whatever is in between J4:J748 (this is how many records are in the document I am working with). I can't figure out how to fix it - any suggestions appreciated. Range("J4").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="Records2", RefersToR1C1:= _ "=Sheet1!R4C10:R748C10" Range("C1").Select
From: Mike H on 24 Mar 2010 09:42 Hi, Try this Dim LastRow As Long Set Sht = Sheets("Sheet1") ' Change to suit LastRow = Cells(Cells.Rows.Count, "J").End(xlUp).Row ActiveWorkbook.Names.Add Name:="Records2", _ RefersTo:=Sht.Range("J4:J" & LastRow) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Munchkin" wrote: > I'm creating a template in which a user can copy & paste their records and > format it a certain way w/a macro button. One portion of the macro should > create a named range for all records that appear in column J, starting at J4. > However, as you can see from my code the marco is always going to name > whatever is in between J4:J748 (this is how many records are in the document > I am working with). > > I can't figure out how to fix it - any suggestions appreciated. > > Range("J4").Select > Range(Selection, Selection.End(xlDown)).Select > ActiveWorkbook.Names.Add Name:="Records2", RefersToR1C1:= _ > "=Sheet1!R4C10:R748C10" > Range("C1").Select
From: Mike H on 24 Mar 2010 09:51 oops, a bug in that one, try this instead Dim LastRow As Long Set Sht = Sheets("Sheet1") ' Change to suit LastRow = Sht.Cells(Cells.Rows.Count, "J").End(xlUp).Row ActiveWorkbook.Names.Add Name:="Records2", _ RefersTo:=Sht.Range("J4:J" & LastRow) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: > Hi, > > Try this > > Dim LastRow As Long > Set Sht = Sheets("Sheet1") ' Change to suit > LastRow = Cells(Cells.Rows.Count, "J").End(xlUp).Row > ActiveWorkbook.Names.Add Name:="Records2", _ > RefersTo:=Sht.Range("J4:J" & LastRow) > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "Munchkin" wrote: > > > I'm creating a template in which a user can copy & paste their records and > > format it a certain way w/a macro button. One portion of the macro should > > create a named range for all records that appear in column J, starting at J4. > > However, as you can see from my code the marco is always going to name > > whatever is in between J4:J748 (this is how many records are in the document > > I am working with). > > > > I can't figure out how to fix it - any suggestions appreciated. > > > > Range("J4").Select > > Range(Selection, Selection.End(xlDown)).Select > > ActiveWorkbook.Names.Add Name:="Records2", RefersToR1C1:= _ > > "=Sheet1!R4C10:R748C10" > > Range("C1").Select
From: JLGWhiz on 24 Mar 2010 10:09 Something like this worked for me. It will allow the named range to vary based on the number of records in column A. Of course it can be altered to suit individual needs. Sub dl() '<<<Can change to CommandButton1_Click() Dim lr As Long, sh As Worksheet, rng As Range Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A2:A" & lr) ActiveWorkbook.Names.Add "TestRange", RefersTo:=rng.Address End Sub "Munchkin" <Munchkin(a)discussions.microsoft.com> wrote in message news:1FE80B99-E504-4969-800A-1CE03705CE76(a)microsoft.com... > I'm creating a template in which a user can copy & paste their records and > format it a certain way w/a macro button. One portion of the macro should > create a named range for all records that appear in column J, starting at > J4. > However, as you can see from my code the marco is always going to name > whatever is in between J4:J748 (this is how many records are in the > document > I am working with). > > I can't figure out how to fix it - any suggestions appreciated. > > Range("J4").Select > Range(Selection, Selection.End(xlDown)).Select > ActiveWorkbook.Names.Add Name:="Records2", RefersToR1C1:= _ > "=Sheet1!R4C10:R748C10" > Range("C1").Select
From: Don Guillett on 24 Mar 2010 10:10
Sub makenamedrange() lr = Cells(Rows.Count, "j").End(xlUp).Row Cells(4, "j").Resize(lr - 3).Name = "Records2" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Munchkin" <Munchkin(a)discussions.microsoft.com> wrote in message news:1FE80B99-E504-4969-800A-1CE03705CE76(a)microsoft.com... > I'm creating a template in which a user can copy & paste their records and > format it a certain way w/a macro button. One portion of the macro should > create a named range for all records that appear in column J, starting at > J4. > However, as you can see from my code the marco is always going to name > whatever is in between J4:J748 (this is how many records are in the > document > I am working with). > > I can't figure out how to fix it - any suggestions appreciated. > > Range("J4").Select > Range(Selection, Selection.End(xlDown)).Select > ActiveWorkbook.Names.Add Name:="Records2", RefersToR1C1:= _ > "=Sheet1!R4C10:R748C10" > Range("C1").Select |