Input Box that allows user to select range

[joecrabtree]

To all,

I have a macro that runs normally from the range J2:J100, and another
for M2:M100 howver this range can change, i.e. could be J2:J50, and
M2:M50. The range is always the SAME for both columns.At the moment I
am going in to edit the code every time I want to change it.

What I want to be able to do is have a pop up box that allows the user
to enter two numbers, one for the range start i.e. 2, and one for the
range stop i.e. 60. (i.e. only specify the row, not the coulmn!). This
would then set the range in the macro as J2:J60, and M2:M60.


Any help would be much appreciated,

Regards,

Joseph Crabtree

 

[Trevor Shuttleworth]

Why don't you post your code and someone will modify it to meet your needs

You could just select the range and establish start and end from that rather
than needing two Input Boxes

Regards

Trevor

 

[Dave Peterson]

As a user, I'd rather point and click at the range than trying to remember the
starting and ending row (and typing them in correctly).

And if you always use column J and column M, you could just pick up one range
and use that as the starting point.

dim Rng1 as range
dim Rng2 as range

set rng1 = nothing
on error resume next
set rng1 = application.inputbox(Prompt:="select the rows to process." & _
vblf & "I'll worry about the columns!", type:=8).areas(1)
on error goto 0

if rng1 is nothing then
'user hit cancel
exit sub
end if

with rng1
set rng1 = intersect(.entirerow,.parent.range("J:J"))
set rng2 = rng1.offset(0,3)
end with

msgbox rng1.address(0,0) & vblf & rng2.address(0,0)

 

[joecrabtree]

Hi

Thanks for your help, however I would prefer not to click and point as
the selection of the range is just the number of rows that the macro
will perform the calculation on. The data is hidden from the user, and
so all I want them to do is be able to input two numbers.

Thanks in advance,

Joseph Crabtree

 

[Dave Peterson]

dim Num1 as long
dim Num2 as long

num1 = application.inputbox(Prompt:="top row is:", type:=1)
num2 = application.inputbox(Prompt:="bottom row is:", Type:=1)

if application.min(num1,num2) < 1 _
or application.max(num1,num2) > activesheet.rows.count then
msgbox "nope!"
exit sub
end if

 

[Dave Peterson]

If the data is hidden from the user, will they be able to guess the right row
numbers?

If yes, how do they do it? Maybe your code could make the same guess and not
bother the user.