Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |