in order to give you a recommendation that has any value,
I need to know what you plan to do with the array.
Your example indicates that you want to create an array
and populate it with the contents? of a range consisting of all cells with
values.
Of course, when more data is added, the array size changes...
ok, that's possible, but not the way you want it to.
the way you change an array size is using ReDim:
'-------------------------------------------------------------------------
Option Explicit
Dim ClaimProvider()
Sub ResizeArray()
Dim rCnt, cCnt
Dim R, C
ReDim ClaimProvider(1, 1)
Sheets("Data").Select
rCnt = ActiveCell.SpecialCells(xlLastCell).Row
cCnt = ActiveCell.SpecialCells(xlLastCell).Column
ReDim ClaimProvider(rCnt, cCnt)
For R = 2 To rCnt
If (R Mod 100 = 0) Then Application.StatusBar = "Loaded " & R & " of "
& rCnt
For C = 1 To cCnt
ClaimProvider(R, C) = Sheets("Data").Cells(R, C).Value
Next C
Next R
Application.StatusBar = False
MsgBox R & " rows loaded"
End Sub
'-------------------------------------------------------------------------
Now, if what you're really trying to do is to define a dynamic RANGE, then
use:
'-------------------------------------------------------------------------
Option Explicit
Sub DefineRange()
Dim rCnt, cCnt, ClaimProviderRange As Range
Sheets("Data").Select
rCnt = ActiveCell.SpecialCells(xlLastCell).Row
cCnt = ActiveCell.SpecialCells(xlLastCell).Column
Set ClaimProviderRange = Sheets("Data").Range(Cells(1, 1), Cells(rCnt,
cCnt))
MsgBox ClaimProviderRange.Rows.Count
End Sub
'-------------------------------------------------------------------------
In this case, the variable ClaimProviderRange works the same as any Range
object.
ClaimProviderRange(1,5).value gives you the value of cell E1
what you're DOING with the array or range will determine the best method for
defining it.
hope this helps.
Paul
________________________________
From: molinari <dh0...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Wed, June 2, 2010 3:59:56 PM
Subject: $$Excel-Macros$$ reading excel worksheet into an array?
Hi all, I want to create an array in VBA such that typical end-users
can modify what goes into the array without knowing code (or looking
at it). Hence, I want the array to read the contents of a worksheet,
excluding the first column and first row (which are labels).
i.e., I want to read the following into the array, but not including
the first row or the first column
# ; Tab (0) ; Code (1); Chart (2)
0 ; Tab1 ; A ; "A Chart"
1 ; Tab2 ; P ; "P Chart"
and the # of rows and columns may increase, as we get more
categories.
How can I accomplish this?
I've tried the following, but run into errors:
Sheets("My Sheet").Select
Dim y As Variant
Dim x As Variant
y = xlLastRow
x = xlLastCol
Dim ClaimProvider() As Variant
ClaimProvider = Range(Cells(2, 2), Cells(y, x))
This isn't working, and gives me errors. (xlLastRow and xlLastCol are
custom functions that find the last row and last column on a worksheet)
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
