1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to choose range, when the desired number of rows/ columns are variables stated on Excel sheet

Discussion in 'VBA Macros' started by lingyai, May 19, 2017.

  1. lingyai

    lingyai New Member

    Messages:
    16
    Hi, I'm more or less a VBA newbie, I mainly record macros and try to adapt them. I'm not having any luck with something which I would imagine would be quite easy to do. If someone could help me with I would be very grateful, I could use this in many different ways.

    The basic idea is being able to define, in VBA, a range, if you know a starting point and a variable # of rows and columns to select from the starting point. These variable numbers would be stated in named cells on a sheet.

    I can easily use the OFFSET excel sheet function to do something sort of similar. If I want, for example, to sum all values in a range whose dimensions I specify on the Excel sheet as variables (# of rows, # of columns etc). So I know Excel understands instructions like "Start from here, move down 3 rows, then right by 4 columns."

    What I'd like the macro to do is simply select a range based on similar instructions, when the arguments could vary.

    Lets say I have three named cells:
    -- "Home" contains nothing, it's just a landmark (locational reference). It could be anywhere on the sheet (though in this example it's in G16.)
    -- "Move_this_many_columns_from_Home" would be validated to accept only integers.
    -- "Move_this_many_rows_from_Home" also would be validated to accept only integer.

    Here's what I'd like th macro to do. It would

    1) go to "Home", start the selection there,

    2) then continue the selection by moving down by the number of rows stated in the "Move_this_many_rows_from_Home" cell if that number is positive (if it's negative, it would move up instead of down)

    3) then continue the selection by moving to the right by the number of columns stated in the "Move_this_many_columns_from_Home" cell, if that number is positive (if it's negative, it would move to the left instead of to the right)

    So if Home is G16,
    "Move_this_many_columns_from_Home" =1, and
    "Move_this_many_rows_from_Home" =5,
    the macro should select cells G16:H21.

    If I then changed "Move_this_many_columns_from_Home" to 4, the macro should select G16:K21.

    Etc.

    I tried to record and then edit a macro, but got stuck when trying to edit. When recording, I used GoTo to get to the "Home" cell -- which is G16 in the attached example -- and then manually moved one column to the right and 5 columns down, resulting in a selection of G16:H21.

    Code (vb):

    Sub Select_range_from_cell_values_01()
        Application.Goto Reference:="Home"
        ActiveCell.Range("A1:B6").Select
    End Sub
     
    I saw that the "6" in "B6" was my "Move_this_many_rows_from_Home" value of 5, plus 1.

    I have no idea how to use actual VBA syntax for this, but the following "pseudo-VBA" gives the idea of what I'd like to do:

    Code (vb):

    Sub Select_range_from_cell_values_01()
        Application.Goto Reference:="Home"
        ActiveCell.Range("A[the value in range_ "Move_this_many_columns_from_Home"]:B[the value in range "Move_this_many_columns_from_Home" + 1 ]).Select
    End Sub
    but I couldn't figure it out.

    Could someone please explain to me how to do this? This would open up a huge number of possibilities for me. I've looked a lot on the web but not found anything which addresses this narrow procedure directly in a way I can undertand.

    Many thanks, and kind regards...
  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    Using the named ranges you already have, you could do it like this:
    Code (vb):
    Sub Select_range_from_cell_values_01()

        Range([home], [home].Offset([Move_this_many_rows_from_Home], [Move_this_many_columns_from_Home])).Select

    End Sub
    Basically you do "Range(first cell, last cell).select"
    In this case the above code reads: "Range(Range("G16"), Range("G16").Offset(5, 1)).Select"
    or, in other words: "Range(Range("G16"), Range("H21")).Select"

    Hope this is comprehensive enough.
    lingyai likes this.
  3. lingyai

    lingyai New Member

    Messages:
    16
  4. lingyai

    lingyai New Member

    Messages:
    16
    Thank you SO much, that works perfectly and is elegant and clear, now that I see it in terms of OFFSET syantax. I'd been looking quite some time, and this is so useful, thanks again sir!

    May I ask two follow up questions, please?

    -- How would I add error-handling if the dimensions requested are impossible, for example, Home is in row C4 and I ask it to slect a range by adding 30 columns to the left and 12 going up?

    --How would the code look if instead of using a named "Home" cell, the starting point would be the selected cell on the worksheet? So you could launch this from anywhere without having to name a cell?
  5. Belleke

    Belleke Member

    Messages:
    112
    You should avoid select or activate in your code, but if that is what you want,
    Can you find some inspiration in something like this.
    See attached
    lingyai likes this.
  6. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    You could use "On error" statements or IF conditions like below (just 2 examples):
    Code (vb):
    Sub errortest()

        On Error GoTo errorhandler
       
        [Home].Offset(-12, -30).Select
       
    errorhandler:
        MsgBox "The desired range can't be selected..."

    End Sub
    or

    Code (vb):
    Sub errortest()

        If [home].Row - 12 <= 0 Or [home].Column - 30 <= 0 Then
            MsgBox "The desired range can't be selected..."
        End If

        [Home].Offset(-12, -30).Select

    End Sub
    Replacing [Home] with "Selection" as below:
    Code (vb):
    Sub Select_range_from_cell_values_01()

        Range(Selection, Selection.Offset([Move_this_many_rows_from_Home], [Move_this_many_columns_from_Home])).Select

    End Sub
    lingyai likes this.
  7. lingyai

    lingyai New Member

    Messages:
    16

    That is way cool. Thanks! Is there an easy way to specify home with a refedit box (selecting the cell) rather than typing in the cell address?
  8. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    A similar question was asked here (particularly the 2nd post)... I suggest you take a look and if you have any further questions feel free to ask :)
    lingyai likes this.
  9. Belleke

    Belleke Member

    Messages:
    112
    Sure my friend,
    You choose the record and we play the music
    See attached
    Chirag R Raval and lingyai like this.
  10. lingyai

    lingyai New Member

    Messages:
    16
    You guys are awesome, thanks a lot!
    Belleke likes this.
  11. Belleke

    Belleke Member

    Messages:
    112
    Thanks for the feedback
    Suc6

Share This Page