Insert Named Range using Excel Macro

[lpahal]

Hello,

I have a macro that formats my Excel worksheet but I'm having a problem
figuring out how to do the last step. I need to select all cells in
the worksheet and Insert a Named Range, but the number of rows in the
sheet will be constantly changing.

I tried recording the macro clicking on A1 then using 'Ctrl+Shift+End'
to select all rows in my sheet (A1:J38). This works fine for the sheet
I'm working in, but the macro is recording the physical cells that I'm
choosing. If I then change my sheet to have 200 rows and run the macro
the Named Range will still be A1:J38 even though this sheet is A1:J200.

Here is the macro code:

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:="ARImportFile", RefersToR1C1:= _
"=ARImportFile!R1C1:R38C10"
ActiveWorkbook.Names.Add Name:="ARImportFile", RefersToR1C1:= _
"=ARImportFile!R1C1:R38C10"
Range("A1").Select
End Sub


How can I change the code to make sure the Named Range uses the dynamic
number of rows?

Thanks for your help.
Leanne

 

[Norman Jones]

Hi Ipahal,

Try:

Activesheet.UsedRange.Name = "ARImportFile"