Convert Excel Row Selection Into Range

[Drakken]

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?

 

[Fluff]

How are they selecting the rows?

 

[Drakken]

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

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]

Firstly do not use VBA keywords (such as Rows) as names for your variables, it can cause problems.
How about

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

 

[Drakken]

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:

 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]

You could just use

Intersect(Rng, Range("A:W")).Copy

You rarely need to select/activate anything.

 

[Drakken]

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]

You're welcome & thanks for the feedback.