Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redefining a new Range area
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redefining a new Range area
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 HIS "Paul Silverman" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redefining a new Range area
Hi Paul,
Try: Sub Tester() With ActiveWorkbook .Names.Add Name:="List", RefersToR1C1:=.Sheets("Database"). _ Range("A1").CurrentRegion End With End Sub --- Regards, Norman "Paul Silverman" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with area that is not a range | Excel Worksheet Functions | |||
Redefining the $ icons format | Excel Worksheet Functions | |||
Print area/range | Excel Discussion (Misc queries) | |||
Redefining Styles | Excel Programming | |||
Find Used area of a range | Excel Programming |