none
How to select a range in Excel 2007 and name this range? RRS feed

  • Question

  • Hi, Everybody

    Please see the attached picture which explains my problem.

    Any help will be much appreciated.

    Thanks,

    Leon Lai

    Thursday, January 14, 2016 2:36 PM

Answers

  • Re:  naming a range with VBA

    Maybe...

    Sub test()
      Dim strAddress As String

      strAddress = Selection.Cells.Address(True, True)
      ActiveWorkbook.Names.Add Name:="Range1", RefersTo:="=" & strAddress
    End Sub
    '---

    Jim Cone
    Portland, Oregon USA
    free & commercial excel programs
    https://goo.gl/IUQUN2 (Dropbox)



    • Marked as answer by Leon Lai Friday, January 15, 2016 7:25 AM
    • Edited by James Cone Saturday, October 22, 2016 10:05 PM
    Thursday, January 14, 2016 4:56 PM
  • Sub Macro8()
        Dim range1 As Range
        Set range1 = Range(ActiveCell, ActiveCell.End(xlDown))
        ActiveWorkbook.Names.Add Name:="range1", RefersTo:="=" & range1.Address(External:=True)
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by André Santo Thursday, January 14, 2016 7:32 PM
    • Marked as answer by Leon Lai Friday, January 15, 2016 7:08 AM
    Thursday, January 14, 2016 5:10 PM

All replies

  • Re:  naming a range with VBA

    Maybe...

    Sub test()
      Dim strAddress As String

      strAddress = Selection.Cells.Address(True, True)
      ActiveWorkbook.Names.Add Name:="Range1", RefersTo:="=" & strAddress
    End Sub
    '---

    Jim Cone
    Portland, Oregon USA
    free & commercial excel programs
    https://goo.gl/IUQUN2 (Dropbox)



    • Marked as answer by Leon Lai Friday, January 15, 2016 7:25 AM
    • Edited by James Cone Saturday, October 22, 2016 10:05 PM
    Thursday, January 14, 2016 4:56 PM
  • Sub Macro8()
        Dim range1 As Range
        Set range1 = Range(ActiveCell, ActiveCell.End(xlDown))
        ActiveWorkbook.Names.Add Name:="range1", RefersTo:="=" & range1.Address(External:=True)
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by André Santo Thursday, January 14, 2016 7:32 PM
    • Marked as answer by Leon Lai Friday, January 15, 2016 7:08 AM
    Thursday, January 14, 2016 5:10 PM
  • Hi Hans Vogelaar

    Thanks a lot for your reply.

    This is exactly what I needed and it works perfectly.

    Best Regards,

    Leon

    Friday, January 15, 2016 7:08 AM
  • Hi Jim Cone,

    Thanks for your reply.

    I tried your suggestion and it also works perfectly.

    Best Regards,

    Leon

    Friday, January 15, 2016 7:24 AM