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

 

[Misca]

Try this

Dim PointOne As Integer
Dim PointTwo As Integer

    PointOne = Cells.Find(What:="2009 Data", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row
        
    
    PointTwo = Cells.Find(What:="2009 Data Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row
        
        Range("A" & PointOne & ":F" & PointTwo).Select

 

[victor26086]

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

This worked perfectly. Thank you all!!!

 

[victor26086]

Try this

Dim PointOne As String
Dim PointTwo As String

    PointOne = Cells.Find(What:="<code style="white-space: nowrap;"><code></code></code>2009 Data", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Address
        
    
    PointTwo = Cells.Find(What:="2009 Data Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Address
        
        Range(PointOne & ":" & PointTwo).Select

I see what your trying to do with this and i would love if it worked but it doesnt highlight the range.

 

[Misca]

You were too fast! Right after I had posted my code I saw your new info and edited the code a little bit. Try the new one. The one you tried seems to be the orginal one, which selects the two cells and the range between these two. The new one only grabs the row numbers and uses the static columns.

 

[victor26086]

You were too fast! Right after I had posted my code I saw your new info and edited the code a little bit. Try the new one. The one you tried seems to be the orginal one, which selects the two cells and the range between these two. The new one only grabs the row numbers and uses the static columns.

Hi, Thanks again. I guess i copied it before you had made the changes...

Anyways the last code you wrote almost worked perfectly. I only changed the code for it could find the whole text, otherwise it was finding "2009 data" twice.

here is your code modified. Thanks a lot I now have both macros i needed.

Sub highlightavariablerange()
Dim PointOne As Integer
Dim PointTwo As Integer

    PointOne = Cells.Find(What:="2009 Data", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row
        
    
    PointTwo = Cells.Find(What:="2009 Data Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row
        
        Range("A" & PointOne & ":F" & PointTwo).Select
End Sub

 

[Phox]

No offense, but why is that macro needed if you already have a named range for the same range?

Range("RANGEone").Select

should work just fine.

 

[victor26086]

No offense, but why is that macro needed if you already have a named range for the same range?

Range("RANGEone").Select

should work just fine.

the second macro is just so i can know how to do otherstuff, (modify it) Im wasnt planing on using both for the same project, but in two different. In my second project I dont want to name the range, just select for other purposes. It also helps me understand how to read and write macros