Test to see if selected range is single row?
 

I have a macro that asks the user to highlight the first row of data
in the worksheet so it can find and separate the worksheet into
several smaller sheets based on the identifier in the first column. I
want to be sure that the user has only selected a single row in the
input box before the rest of the macro executes. I could write code to
get the row number for the top row of the selected range and the row
number for the last row in the selected range and compare them, but
this seems more complicated than it needs to be. Is there an easier
way? Some kind of simple IF statement that tells the user to try again
if there's more than one row in their selection?

Thanks!

Test to see if selected range is single row?
 

If Selection.Rows.Count 1 Then

RBS

"davegb" wrote in message
ups.com...
I have a macro that asks the user to highlight the first row of data
in the worksheet so it can find and separate the worksheet into
several smaller sheets based on the identifier in the first column. I
want to be sure that the user has only selected a single row in the
input box before the rest of the macro executes. I could write code to
get the row number for the top row of the selected range and the row
number for the last row in the selected range and compare them, but
this seems more complicated than it needs to be. Is there an easier
way? Some kind of simple IF statement that tells the user to try again
if there's more than one row in their selection?

Thanks!

Test to see if selected range is single row?
 

If Selection.Rows.Count1 or Selection.Areas.Count1 then
HTH, James
"davegb" wrote in message
ups.com...
I have a macro that asks the user to highlight the first row of data
in the worksheet so it can find and separate the worksheet into
several smaller sheets based on the identifier in the first column. I
want to be sure that the user has only selected a single row in the
input box before the rest of the macro executes. I could write code to
get the row number for the top row of the selected range and the row
number for the last row in the selected range and compare them, but
this seems more complicated than it needs to be. Is there an easier
way? Some kind of simple IF statement that tells the user to try again
if there's more than one row in their selection?

Thanks!

Test to see if selected range is single row?
 

If selection.Rows.Count1 then
Msgbox "Select only one row"
Else
'process the selected row
End if


--

Hope that helps.

Vergel Adriano


"davegb" wrote:

I have a macro that asks the user to highlight the first row of data
in the worksheet so it can find and separate the worksheet into
several smaller sheets based on the identifier in the first column. I
want to be sure that the user has only selected a single row in the
input box before the rest of the macro executes. I could write code to
get the row number for the top row of the selected range and the row
number for the last row in the selected range and compare them, but
this seems more complicated than it needs to be. Is there an easier
way? Some kind of simple IF statement that tells the user to try again
if there's more than one row in their selection?

Thanks!

Test to see if selected range is single row?
 

On Mar 19, 1:36 pm, "RB Smissaert"
wrote:
If Selection.Rows.Count 1 Then

RBS

"davegb" wrote in message

ups.com...


Works great, Thanks!



I have a macro that asks the user to highlight the first row of data
in the worksheet so it can find and separate the worksheet into
several smaller sheets based on the identifier in the first column. I
want to be sure that the user has only selected a single row in the
input box before the rest of the macro executes. I could write code to
get the row number for the top row of the selected range and the row
number for the last row in the selected range and compare them, but
this seems more complicated than it needs to be. Is there an easier
way? Some kind of simple IF statement that tells the user to try again
if there's more than one row in their selection?

Thanks!- Hide quoted text -

- Show quoted text -

Test to see if selected range is single row?
 

Instead of yelling at the user if he/she selected a range with multiple rows,
you could just take the top row:

dim myRng as range
set myrng = nothing
on error resume next
set myrng _
= application.inputbox(Prompt:="select a range",type:=8).areas(1).rows(1)
on error goto 0
if myrng is nothing then
'user hit cancel
else
'do what you want
end if

Or just the first cell -- or the entire row of the first cell -- or anything you
want.

davegb wrote:

I have a macro that asks the user to highlight the first row of data
in the worksheet so it can find and separate the worksheet into
several smaller sheets based on the identifier in the first column. I
want to be sure that the user has only selected a single row in the
input box before the rest of the macro executes. I could write code to
get the row number for the top row of the selected range and the row
number for the last row in the selected range and compare them, but
this seems more complicated than it needs to be. Is there an easier
way? Some kind of simple IF statement that tells the user to try again
if there's more than one row in their selection?

Thanks!

--

Dave Peterson

Test to see if selected range is single row?
 

On Mar 19, 2:10 pm, Dave Peterson wrote:
Instead of yelling at the user if he/she selected a range with multiple rows,
you could just take the top row:


Actually, I wasn't going to yell at them... :)

Interesting point. I just need to know that the word "Adams" is
somewhere in the first row of the range they selected. If not, they
need to try again. If so, we're good to go. Think I'll take your
suggestion.
Thanks, Dave!

dim myRng as range
set myrng = nothing
on error resume next
set myrng _
= application.inputbox(Prompt:="select a range",type:=8).areas(1).rows(1)
on error goto 0
if myrng is nothing then
'user hit cancel
else
'do what you want
end if

Or just the first cell -- or the entire row of the first cell -- or anything you
want.

davegb wrote:

I have a macro that asks the user to highlight the first row of data
in the worksheet so it can find and separate the worksheet into
several smaller sheets based on the identifier in the first column. I
want to be sure that the user has only selected a single row in the
input box before the rest of the macro executes. I could write code to
get the row number for the top row of the selected range and the row
number for the last row in the selected range and compare them, but
this seems more complicated than it needs to be. Is there an easier
way? Some kind of simple IF statement that tells the user to try again
if there's more than one row in their selection?

Thanks!

--

Dave Peterson