Setting a range?

J

jayklmno

What is wrong with this code?

Dim MyArray As Variant
Dim MyRange As Range
Dim EndRow As Long
Dim EndCol As Integer

Debug.Print Sheets("Data").Range("A1")
EndRow = Sheets("Data").Range("A1").End(xlDown).Row
EndCol = Sheets("Data").Range("A1").End(xlToRight).Column

Set MyRange = Worksheets("Data").Range(Cells(1, 1), Cells(EndRow, EndCol))
MyArray = MyRange

The Set MyRange line errors out with an Runtime Error. EndRow and EndCol are
valid locations, so it has to be how I use the cells in the range statement?
Any help?
 
T

Trevor Shuttleworth

Works for me.

What values do you get for EndRow and EndCol ?

What error message do you get ?

Regards

Trevor
 
D

Dave Peterson

If Data isn't the activesheet, you'll have trouble:

Set MyRange = Worksheets("Data").Range(worksheets("data").Cells(1, 1), _
worksheets("data").Cells(EndRow, EndCol))

or
with worksheets("Data")
Set MyRange = .Range(.Cells(1, 1), .Cells(EndRow, EndCol))
end with

or even...
with worksheets("Data")
Set MyRange = .Range("A1", .Cells(EndRow, EndCol))
end with

And I like to explicitly use the property:

myArray = myRange.value

=====
And it turns out that "As Integer" actually slows the pc down. Internally, the
pc will convert it to long.

Dim EndCol as Long
is safer, faster and easier to type <bg>
 
J

jayklmno

Try running it when it isn't the active sheet. My EndRow and EndCol values
are valid, but it is getting an Runtime 1004 - Application-defined or object
defined error.

This macro is accessing data on another tab in the spreedsheet, not the
active tab.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top