define/name a rnge in code

[HobbitK]

Hi everyone...
I want to define and name a range in code that is based on the ActiveCell.
I am searching all my resources, but thought a pst may help someone else as well.
Thanks
Michael

 

[RobBroekhuis]

To assign a name to the currently active range, use
names.add "MyName",selection
Rob

 

[SkipVought]

hi,

Here's another approch that combines naming a selection (in this case the current region contiguous with cell(1,1)) and also naming the column ranges with column heading names...

Sub NameTheRanges()
    Dim sTableName As String
    On Error Resume Next
    sTableName = "MyTableName"
    With ActiveSheet.Cells(1, 1).CurrentRegion
        ActiveWorkbook.Names.Add _
            Name:=sTableName, _
            RefersTo:="=" & .Name & "!" & .Address
        .CreateNames _
            Top:=True, _
            Left:=False, _
            Bottom:=False, _
            Right:=False
    End With
End Sub

Hope this helps Skip,
Skip@theofficeexperts.com

 

[HobbitK]

Skip ...
Would you mind explaining that code to me? I am brand new to VBA and I think you lost me at the With statement!
I understand With and End With .. thats easy
ActiveSheet is self-explanatory
.Cells I can probably understand what is being referenced

(1, 1).CurrentRegion Need Input! Where did the current region get defined and what is it?? and why (1, 1) ?

The rest of it is way over my head right now !!
Need LOTS OF INPUT ON THAT !!!

Thanks
Michael

 

[SkipVought]

HobbitK,
Tables and lists ought to, by convention,
1. start in the upper-left hand of a sheet -- ie A1 or Cells(1, 1). I prefer Cells(row, col) because number are easier to use than column letter disignation in code.
2. have one row of headings
3. have no empty rows or columns (ie must be contiguous)

Look up CurrentRegion in Help. There is also a toolbar icon that does CurrentRegion.

Here's another technique that I use when I have multiple tables in a workbook...

1. one table per sheet
2. the sheet name becomes the table name (I often use sheet/table names like tblCustomerMaster, tblCustomerDetail -- then in the Workbook_SheetChange event, I call that above coded procedure

IF Left(Sh.Name, 3) = "tbl" Then NameTheRanges

-- change the procedure to

sTableName = Sh.Name

Hope this helps Skip,
Skip@theofficeexperts.com