Redefining a new Range area

[Paul Silverman]

Hi Folks,
I'm creating a macro where, each time it's run, I have to delete and
redefine a range (called List) which keeps growing.

I recorded it as follows:-
ActiveWorkbook.Names("List").Delete
Sheets("Database").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="List", RefersToR1C1:= _
"=Database!R1C1:R3781C6"

In the macro, the range is fixed at row 3781, but of course this will grow.

Is there some VBA code I can plug in which will select the entire range,
redefining the size as the range grows?

Thanks,

Paul Silverman
Melbourne Australia

 

[Nick Hodge]

Paul

The code below will define a dynamic range and name it 'List'. There is
seldom any need to select anything but as that was what you requested that
is included. (This results in the code to activate the sheet as you can
select a range on a sheet that is not activated).

Sub redefineRange()
Dim lLastRow As Long
Dim iLastColumn As Integer
ThisWorkbook.Names("List").Delete
With Worksheets("Database")
lLastRow = .Range("A65536").End(xlUp).Row
iLastColumn = .Range("IV1").End(xlToLeft).Column
.Activate
End With
Range(Cells(1, 1), Cells(lLastRow, iLastColumn)).Select
ThisWorkbook.Names.Add Name:="List", RefersToR1C1:=Selection
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Norman Jones]

Hi Paul,

Try:

Sub Tester()
With ActiveWorkbook
.Names.Add Name:="List", RefersToR1C1:=.Sheets("Database"). _
Range("A1").CurrentRegion
End With
End Sub