Setting a range?
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
jayklmno wrote:
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?
--
Dave Peterson
|