Vatiable range

[Guest]

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

 

[Norman Jones]

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
'<<=============

 

[Don Guillett]

Why not just use a defined name to self adjust the range
insert>name>define>name 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

 

[Mike Fogleman]

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

 

[Guest]

Many Thanks !

oldjay

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
'<<=============