Named Range

[Munchkin]

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

 

[Mike H]

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.

 

[Mike H]

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.

 

[JLGWhiz]

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

 

[Don Guillett]

Sub makenamedrange()
lr = Cells(Rows.Count, "j").End(xlUp).Row
Cells(4, "j").Resize(lr - 3).Name = "Records2"
End Sub

 

[Brad E.]

Munchkin,

You have already Selected the range you want to name, so the following
should work for you.

ActiveWorkbook.Names.Add Name:="Records2", RefersTo:=Selection.Address

I did not test this. -- Brad E.