CODEWITHSUNDEEP
excelvbarangeselection
Hahasiah
Hahasiah

Reputation: 27

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

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

Upvotes: 1

Views: 355

Answers (2)

brettdj
brettdj

Reputation: 55702

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

Upvotes: 0

Michael
Michael

Reputation: 4883

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

Upvotes: 2

Related Questions