Selecting a range using macros

kwaring · Mar 15th, 2002, 08:40 PM

i need to be able to select a range using a macro. however, the range is going to vary according to some other cells. ie say the value in cell A1 is 5 and B1 is 10 and i want to select from I5 to I10 referencing A1 and B1, how do i do it?
thanx

Tom Urtis · Mar 15th, 2002, 09:18 PM

Try something like this. The error trap is in case a non-valid value (a number less than 1 or greater than 65536, or a letter or symbol, etc) is in A1 or B1.

Sub SelectRange()
On Error GoTo e
Range("I" & [A1] & ":" & "I" & [B1]).Select
Exit Sub
e:
MsgBox "Place a valid numeral in A1 or B1.", 48, "Can't determine range"
End Sub

kwaring · Mar 15th, 2002, 09:21 PM

works like a charm! thanks

Mar 15th, 2002, 09:18 PM	#2
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,384	Try something like this. The error trap is in case a non-valid value (a number less than 1 or greater than 65536, or a letter or symbol, etc) is in A1 or B1. Sub SelectRange() On Error GoTo e Range("I" & [A1] & ":" & "I" & [B1]).Select Exit Sub e: MsgBox "Place a valid numeral in A1 or B1.", 48, "Can't determine range" End Sub __________________ Tom Urtis Microsoft MVP - Excel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 15th, 2002, 08:40 PM	#1
kwaring New Member Join Date: Mar 2002 Location: Nanaimo, BC Posts: 49	i need to be able to select a range using a macro. however, the range is going to vary according to some other cells. ie say the value in cell A1 is 5 and B1 is 10 and i want to select from I5 to I10 referencing A1 and B1, how do i do it? thanx

Mar 15th, 2002, 09:21 PM	#3
kwaring New Member Join Date: Mar 2002 Location: Nanaimo, BC Posts: 49	works like a charm! thanks