Convert Excel Row Selection Into Range

Drakken

New Member
Joined
May 14, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
60,138
Office Version
  1. 365
Platform
  1. Windows
How are they selecting the rows?
 

Drakken

New Member
Joined
May 14, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
60,138
Office Version
  1. 365
Platform
  1. Windows
Firstly do not use VBA keywords (such as Rows) as names for your variables, it can cause problems.
How about
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
Joined
May 14, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
60,138
Office Version
  1. 365
Platform
  1. Windows
You could just use
VBA Code:
Intersect(Rng, Range("A:W")).Copy
You rarely need to select/activate anything.
 

Drakken

New Member
Joined
May 14, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
60,138
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top