Vatiable range
I need to expand or reduce a range named "Database
I need to use (I guess) OffSet to select 4 adjacent col's I started with this Range("B6").Select Range(Selection, Selection.End(xlDown)).Select Range("Database").CurrentRegion.Name = "Database" How do I do this? oldjay |
Vatiable range
Hi Oldjay,
Try something like: '============= Public Sub Tester() Dim SH As Worksheet Dim Rng As Range Dim iRow As Long Set SH = ActiveSheet '<<==== CHANGE With SH iRow = .Range("B" & Rows.Count).End(xlUp).Row Set Rng = .Range("B2:E" & iRow) Rng.Name = "Databse" End With End Sub '<<============= --- Regards, Norman "Oldjay" wrote in message ... I need to expand or reduce a range named "Database I need to use (I guess) OffSet to select 4 adjacent col's I started with this Range("B6").Select Range(Selection, Selection.End(xlDown)).Select Range("Database").CurrentRegion.Name = "Database" How do I do this? oldjay |
Vatiable range
Why not just use a defined name to self adjust the range
insertnamedefinename it as desired "myrng"in the refers to box =offset($a$1,1,0,counta($a:$a)-1,6) look in the help index for OFFSET. Modify to suit BTW. In your macros, selections are RARELY necessary or desirable -- Don Guillett SalesAid Software "Oldjay" wrote in message ... I need to expand or reduce a range named "Database I need to use (I guess) OffSet to select 4 adjacent col's I started with this Range("B6").Select Range(Selection, Selection.End(xlDown)).Select Range("Database").CurrentRegion.Name = "Database" How do I do this? oldjay |
Vatiable range
To supplement Norman's code to work with unknown column numbers also:
Public Sub Tester() Dim SH As Worksheet Dim Rng As Range Dim iRow As Long Dim iCol As Long Set SH = ActiveSheet '<<==== CHANGE iCol = Cells(2, Columns.Count).End(xlToLeft).Column With SH iRow = .Range("B" & Rows.Count).End(xlUp).Row Set Rng = .Range((Cells(2, 2)), (Cells(iRow, iCol))) Rng.Name = "Databse" End With End Sub Mike F "Oldjay" wrote in message ... I need to expand or reduce a range named "Database I need to use (I guess) OffSet to select 4 adjacent col's I started with this Range("B6").Select Range(Selection, Selection.End(xlDown)).Select Range("Database").CurrentRegion.Name = "Database" How do I do this? oldjay |
Vatiable range
Many Thanks !
oldjay "Norman Jones" wrote: Hi Oldjay, Try something like: '============= Public Sub Tester() Dim SH As Worksheet Dim Rng As Range Dim iRow As Long Set SH = ActiveSheet '<<==== CHANGE With SH iRow = .Range("B" & Rows.Count).End(xlUp).Row Set Rng = .Range("B2:E" & iRow) Rng.Name = "Databse" End With End Sub '<<============= --- Regards, Norman "Oldjay" wrote in message ... I need to expand or reduce a range named "Database I need to use (I guess) OffSet to select 4 adjacent col's I started with this Range("B6").Select Range(Selection, Selection.End(xlDown)).Select Range("Database").CurrentRegion.Name = "Database" How do I do this? oldjay |
All times are GMT +1. The time now is 03:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com