How to set a range of the cells selected in that moment

Question

I'm trying to set a range of cells that are already selected is there a way to do it?

    Range("e2").Select
    Range(Selection.End(xlToRight), Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Set temprange = activeselection

I had to select that way because in the sheet there aren't data in all columns always but in E it have to and the data starts in row 2 it work fine but i need to set it as a range in other to pass a filter on it.

Activeselection was too easy so i should know that it wasn't going to work that way.. What is the proper way to do it? Thanks

`Set temprange = Selection` - but please think twice (and then twice more) before building a lot of code around `Select` or `Activate`. (E.g. are you just trying to `Set temprange = Range("E2").CurrentRegion`?) — YowE3K, Feb 08 '17 at 01:46
It worked with both selection and with currentregion .Thanks, whats the diference bettewen them? And how can i mark your comment as an answer? :D — Hahasiah, Feb 08 '17 at 01:59
Theoretically, the answer to your question is just the `Set temprange = Selection` part of my comment (so you can mark @Michael's answer as correct). I doubt if the `Select` statements you currently use will **always** end up with `CurrentRegion`, so that part of my comment has nothing to do with the question, and is just a suggestion on what you should probably really be doing to achieve what you are wanting to do. — YowE3K, Feb 08 '17 at 02:04
P.S. See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/q/10714251/6535336) for some very good tips on ... wait for it ... how to avoid using `Select` in Excel VBA macros. — YowE3K, Feb 08 '17 at 02:08

Michael · Accepted Answer · 2017-02-08T03:55:56.833

If you want to refer to cells that are ALREADY selected, just use the Selection reference:

Set temprange = Selection

Note that it's generally better to avoid working with selections in VBA where possible and simple work with ranges. It's possible for you to rewrite the code to not work with selections at all:

Set temprange = Range(Range("E2").End(xlToRight), Range("E2").End(xlDown))
Set temprange = Range(temprange, temprange.End(xlToLeft))
Set temprange = Range(temprange, temprange.End(xlToRight))

You could probably also simplify this further using offsets to automatically include 2 additional columns to the right. See the article below for more details about how to reference ranges in VBA:

https://msdn.microsoft.com/en-us/library/office/gg192736(v=office.14).aspx

score 0 · Answer 2 · answered Feb 08 '17 at 04:42

An option is to provide this choice to select a range to the user, with the current selection already populated as default:

code

Sub GetRng()
Dim rng1 As Range
On Error Resume Next
Set rng1 = Application.InputBox("pls select range", "Set Range", Selection.Address, , , , , 8)
On Error GoTo 0
If rng1 Is Nothing Then MsgBox "User cancelled", vbCritical
End Sub

How to set a range of the cells selected in that moment

2 Answers2