# Convert Excel Row Selection Into Range

#### Drakken

##### New Member
Hello!

I am currently taking a user input utilizing the InputBox function. The user is supposed to select the rows they want to look at and then I want to use that selection to print out data within that selection. There is a lot more going on between the selection and the printing, but this is the basic function I am currently trying to figure out. The columns will always include A to W without variation. So... I want to take the users' row selection and convert that into a usable range that includes columns A to W.

As an example:

- User selects they want rows 8 through 10
- Take InputBox output and convert (\$8:\$10) to (\$A\$8:\$W\$10)

Is there an easy way to do this?

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Fluff

##### MrExcel MVP, Moderator
How are they selecting the rows?

#### Drakken

##### New Member
Using VBA, I have the variable "rows" set as a variant and use the following:

VBA Code:
``Set rows = Application.InputBox("Instructions","Select rows of data", Type:=8)``

Type 8 is for ranges. So the input box pops up when the macro is started and there's an entry field where the rows can be entered by typing or simply by clicking and highlighting the rows.

#### Fluff

##### MrExcel MVP, Moderator
Firstly do not use VBA keywords (such as Rows) as names for your variables, it can cause problems.
VBA Code:
``````   Dim Rng As Range

Set Rng = Application.InputBox("Instructions", "Select rows of data", Type:=8)
Set Rng = Intersect(Rng, Range("A:W"))``````

#### Drakken

##### New Member

Yeah I could see how that could be an issue, I will be sure to correct that!

Also, thank you for the suggestion! The intersect function worked . I had not come across that function during my internet searches or ever used it before. I did end up having to use it in the following way though:

VBA Code:
`````` Dim Rng As Range

Set Rng = Application.InputBox("Instructions", "Select rows of data", Type:=8)
Intersect(Rng, Range("A:W")).Select
Selection.Copy``````

This was my method of transferring the data within that range to another worksheet. I was not sure how else to do it if there is another way.

Thanks again for your help Fluff!

#### Fluff

##### MrExcel MVP, Moderator
You could just use
VBA Code:
``Intersect(Rng, Range("A:W")).Copy``
You rarely need to select/activate anything.

#### Drakken

##### New Member
Oh my, I feel dumb. Yeah I have tried to refrain from selecting anything but saw an example and ran with it without thinking. I will definitely change that

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
0
Views
21
Replies
0
Views
22
Replies
3
Views
30
Replies
1
Views
115
Replies
5
Views
41

1,136,803
Messages
5,677,818
Members
419,722
Latest member
Rizzol

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back