Can i set a group of cells as an "object" or "range" for later use?

[jtsains]

I have been searching and can't find my answer on this.

I want to set a group of cells as a range. Here is my code.

Sub test()
Dim dataset As Range
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
dataset = Selection
end sub

I want to be able to use that set of data later in my code to set the values. I can use Selection.copy but it isn't as clean as using a range.

Thank you for your help!!

 

[Bairn90]

For objects you can't simply say object/range = x. You need to use Set object/range = x

set dataset = selection

*Edit* Also, what Jomo said. Much better explanation.

 

[Jonmo1]

Just need to add the word Set to the last line

Set dataset = Selection


However, it's almost never necessary to "Select" ranges to work with them.
So that code can be improved a bit like this

Sub test()
Dim dataset As Range

Set dataset = Range(Range("A1"), Range("A1").End(xlDown))
end sub

 

[jtsains]

Jonmo1:
When I use your code I get a Run-Time error '1004':
Application-Defined or object-defined error.

Sub test()
Dim dataset As Range
Set dataset = Range(Range("A1"), Range("A1").End(x1down))

Range("A1").Select
ActiveCell.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Date
ActiveCell.Offset(1, 0).Value = user
ActiveCell.Offset(2, 0).Value = dataset
End Sub

When I run this code with just adding the "Set" option my data doesn't show up when I tell it to add the data to a new location:

Sub test()
Dim dataset As Range
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Set dataset = Selection
Range("A1").Select
ActiveCell.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Date
ActiveCell.Offset(1, 0).Value = user
ActiveCell.Offset(2, 0).Value = dataset
End Sub
[code/]

Any suggestions?

 

[Jonmo1]

That's not the code I posted..look VERY closely at the xldown part..

I had xldown (the letter L)
You have x1down (the number 1)

 

[jtsains]

Wow, sorry I missed that. I do not get the error now; however, it isn't adding the dataset to the new location identified in the rest of the code. Any suggestions for this?

Sub test2()
Dim dataset As Range
Set dataset = Range(Range("A1"), Range("A1").End(xlDown))
Range("A1").Select
ActiveCell.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Date
ActiveCell.Offset(1, 0).Value = user
ActiveCell.Offset(2, 0).Value = dataset

End Sub

</PRE>

 

[Jonmo1]

I don't really understand what it is you want to "DO" with the dataset range ?
Can you describe in words what you want to do.
Instead of with code that doesn't do what you want

 

[jtsains]

Sorry. I'm trying to do basically the same thing that coping the data and then pasting it in a new location.

I'm trying not to use just copy/paste because i'm not always pasting the data in the new location right away and sometimes need to use the copy and paste option for emediate data transfers, etc.

Thank you so much for your help!

 

[Jonmo1]

Try

Sub test2()
Dim dataset As Range
With Range("A1")
    Set dataset = Range(.Cells(1, 1), .End(xlDown))
    .End(xlToRight).Offset(0, 1).Value = Date
    .End(xlToRight).Offset(1, 0).Value = user
    dataset.Copy .End(xlToRight).Offset(2, 0)
End With
End Sub

 

[jtsains]

This code does work if I have no other code between when I set the dataset and when I paste the data.
At times I will set different ranges from different sheets at one time and then go to a final sheet and paste each range. I am trying to make it a little more clean than all the back and forth between sheets and copy/pasting, etc.

for example in some cases my purpose for needing this code is:

sub test()
'Current sheet is Sheet1
dim dataset as range
dim numberset as range
dim alpha as range

Sheets("Sheet1").select
With Range("A1")
Set  numberset = Range(.Cells(1,1), .End(xlDown))
End With

Sheets("Sheet2").Select
With Range("A1")
Set numberset= Range(.Cells(1,1), .End(xlDown))
End With

Sheets("Sheet3").Select
With Range("A1")
Set  alpha = Range(.Cells(1,1), .End(xlDown))
End With

Sheets("Sheet4").Select '(in a different workbook sometimes)
Range("A1").Select
ActiveCell.End(xltoright).Select
ActiveCell.Offset(0,1).Select
ActiveCell.Value = Dataset

Range("A1").Select
ActiveCell.End(xltoright).Select
ActiveCell.Offset(0,1).Select
ActiveCell.Value = Numberset

Range("A1").Select
ActiveCell.End(xltoright).Select
ActiveCell.Offset(0,1).Select
ActiveCell.Value = alpha

End Sub