none
How to define a range of cells within a named range object RRS feed

  • Question

  • It's easy to select any given cell in a named range using numerical row and column references; thus:

    Sub Ztest()
    Dim Test as range
    Set Test = Range("C5:K12")
    Test(3,5).Select  'selects worksheet cell G7
    End Sub

    However, I want to select a range of contiguous cells within the named range object "Test", such as worksheet cells D7:F10. This range corresponds to cells 3,2 to 6,4 in "Test". However, I can't find any syntax to do this, something like  Test((3, 2):(6, 4)),  which doesn't work. Am I asking for the impossible? If not, what is the syntax that will let me use numerical row and column references (discrete or in variables) to select multiple cells within a named range object?

    Thanks in advance.



    • Edited by Rabnud Monday, April 4, 2016 8:13 PM correct a typo
    Monday, April 4, 2016 8:10 PM

Answers

  • You can use

    Test.Range("B3:D6").Select

    or

    Test.Cells(3, 2).Resize(4, 3).Select

    or

    Test.Range(Cells(3, 2), Cells(6, 4)).Select


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

    • Marked as answer by Rabnud Tuesday, April 5, 2016 8:59 PM
    Monday, April 4, 2016 8:26 PM

All replies

  • You can use

    Test.Range("B3:D6").Select

    or

    Test.Cells(3, 2).Resize(4, 3).Select

    or

    Test.Range(Cells(3, 2), Cells(6, 4)).Select


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

    • Marked as answer by Rabnud Tuesday, April 5, 2016 8:59 PM
    Monday, April 4, 2016 8:26 PM
  • Hi, Rabnud

    You can use following lines of code to get your desired output.

    Sub demo()
    ActiveSheet.Range(Cells(3, 2), Cells(6, 4)).Select
    ActiveSheet.Range(Cells(4, 5), Cells(12, 6)).Select
    End Sub
    

    For getting more information regarding this topic please visit the link below.

    How to select cells/ranges by using Visual Basic procedures in Excel

    How to select cells/ranges by using Visual Basic procedures in Excel

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 5, 2016 6:02 AM
    Moderator
  • Thanks, Hans,

    The third method is what I was looking for - I can now manipulate a block of cells within a range using numerical row and column references. The second, using Resize, can be made to work (I just tried it, but it's awkward)

    Tuesday, April 5, 2016 9:03 PM
  • Thanks, Deepak,

    Unfortunately your scheme doesn't seem to include or relate to a declared, named range.

    Tuesday, April 5, 2016 9:06 PM