Input Box that allows user to select range
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 wrote:
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
--
Dave Peterson
|