• 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...

  • 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

lingyai

New Member
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:
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:
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...
 

Attachments

  • Defining and selecting a range using parameters on excel sheet_v02_for Chandoo forum.xlsm
    31.6 KB · Views: 2
Hi,

Using the named ranges you already have, you could do it like this:
Code:
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.
 
Hi,

Using the named ranges you already have, you could do it like this:
Code:
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.
 
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?
 
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
 

Attachments

  • Defining and selecting a range using parameters on excel sheet_v02_for Chandoo forum.xlsm
    36.1 KB · Views: 5
-- 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?

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

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

End Sub

or

Code:
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

--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?

Replacing [Home] with "Selection" as below:
Code:
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
 
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


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?
 
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?
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 :)
 
Sure my friend,
You choose the record and we play the music
See attached
 

Attachments

  • Defining and selecting a range using parameters on excel sheet_v02_for Chandoo forum.xlsm
    34.3 KB · Views: 7
Back
Top