Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: How to calculate A1-style ranges

  1. #1
    Guest

    Default

    How do you create a reference to a range where you need to calculate the bottom right-hand corner of the range? For example, I know the range starts at J8, I know the right-hand edge of the range is column L, and I have a variable that contains the number of rows. I need a reference that looks like:
    Range ("J8:L230")
    but using the variable instead of 230. How do I do this?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    There are various methods you can use:- How about using the Resize property e.g.

    Sub ExtendRange()
    Dim rngeLeftTopCell As Range, rngeNewRange As Range
    Dim lngNumberOfColumns As Long, lngNumberOfRows As Long

    'This is the top left cell of the current selection
    Set rngeLeftTopCell = Range("J8")

    'Number of rows and columns you want the range increased by
    lngNumberOfRows = 223
    lngnumberofcols = 3

    Set rngeNewRange = rngeLeftTopCell.Resize(lngNumberOfRows, lngnumberofcols)

    MsgBox rngeNewRange.Address


    End Sub

    HTH,
    D

    [ This Message was edited by: dk on 2002-02-17 04:12 ]

  3. #3
    Guest

    Default

    If your variable is represented by the letter v :-
    Range ("J8:L" & v)

  4. #4
    Guest

    Default

    Two excellent solutions. Thank you both.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •