Name a range using macro

[victor26086]

I have a small spreadsheet and im trying to find the fastest way to name a range using a macro.

The way i am currently finding my far right top point of the range is using "FInd" and then ctrl left.
The name is always on column C, in this case the name on top is "2009 Data" so thats what i search for. Like this.....

   Cells.Find(What:="2009 Data", LookAt:=xlWhole, SearchDirection:=xlNext).Activate
    Selection.End(xlToLeft).Select

The way I want to find my lower right side point of the range is like this...

 Cells.Find(What:="2009 Data Total", LookAt:=xlWhole, SearchDirection:=xlNext).Activate
    Selection.End(xlToRight).Select

Please help if you can. Thanks

 

[victor26086]

This macro does not work but hopefully you can see what im trying to do to highlight the area were the range needs to be named

Sub Macro15()

Dim LR As Range, LR1 As Range
    LR = Cells.Find(What:="2009 Data", LookAt:=xlWhole, SearchDirection:=xlNext).Activate
    Selection.End(xlToLeft).Select


    LR1 = Cells.Find(What:="2009 Data Total", LookAt:=xlWhole, SearchDirection:=xlNext).Activate
    Selection.End(xlToRight).Select
    
    Range(LR & LR1).Select
    
    
    'then from here once the range is highlighted I need to name the range "RANGEone"
End Sub

 

[victor26086]

If that im asking is not easy to understand, can you please tell me...

Basically if you can show me how to select a range from point one to point two that would help as well.

 

[sous2817]

Is your data on one row or one column, or can "2009 Data" be in cell A1 and "2009 Data Total" om cell G45?

 

[Misca]

You don't need to select the found cells / ranges. What you're really after are the cell addresses. So instead of .select get the .address.

Once you know the cell addresses you can easily set the range between these two cells.

 

[victor26086]

the data starts in column A and ends in column F.

the only variables are the rows.

"2009 Data" and "2009 Data Total" is always in column C

Thanks

 

[victor26086]

You don't need to select the found cells / ranges. What you're really after are the cell addresses. So instead of .select get the .address.

Once you know the cell addresses you can easily set the range between these two cells.

I think you maybe right but im not sure how to name the syntex

(Range(point one & point 2))

 

[sous2817]

How about this:

Sub Macro1()
'
' Macro1 Macro
'

'
Dim LRTop as Range, LRBottom as Range

    LRTop = Cells.Find(What:="2009 Data", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(0, -2).Address
        
        
        
    LRBottom = Cells.Find(What:="2009 Data Total", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(0, 3).Address

ActiveWorkbook.Names.Add Name:="RangeOne", RefersTo:=Range(LRTop & ":" & LRBottom)
End Sub

 

[victor26086]

thanks for your help,

its not working for me

 

[Phox]

Try this:

Sub NameMyRange()

    Dim rngTopLeft As Range, rngBottomRight As Range

    Set rngTopLeft = Cells.Find(What:="2009 Data", LookAt:=xlWhole, SearchDirection:=xlNext).End(xlToLeft)
    Set rngBottomRight = Cells.Find(What:="2009 Data Total", LookAt:=xlWhole, SearchDirection:=xlNext).End(xlToRight)
    
    ActiveWorkbook.Names.Add Name:="RANGEone", RefersTo:="=" & Range(rngTopLeft, rngBottomRight).Address
    
End Sub