Dynamic Arrays

Hello,

I am wondering if someone could help me with a question. I am using VBA to count total rows in a spreadsheet, and upon find the MAX number, returning the last row's data. The dilemma is, however, that this data source will continually grow in size. The static method is OK if you always know the range, but it later dawned on me that this is a test source only; production data will be CONSIDERABLY larger. I am toying with the idea of using a dynamic array to do this. I am pretty new to VBA and thought I'd ask for some input. The code below is pretty self explanatory. I am using relative references to do this below.

Appreciate any help you can provide!

__________________________________________________

owb.Worksheets("tester").Select
With ActiveSheet.Range("A1:H500")
.Select
Worksheets.Add
Worksheets("Sheet1").Range("A1").Cells.Value = "Number of rows from A"
Worksheets("sheet1").Range("B1").Cells.Value = "=COUNT(tester!R[1]C[-1]:R[500]C[-1])"
Worksheets("Sheet1").Range("A2").Cells.Value = "Number of rows from B"
Worksheets("sheet1").Range("B2").Cells.Value = "=COUNT(tester!RC:R[499]C)"
Worksheets("Sheet1").Range("A3").Cells.Value = "Number of rows from C"
Worksheets("sheet1").Range("B3").Cells.Value = "=COUNT(tester!R[-1]C[1]:R[498]C[1])"
Worksheets("Sheet1").Range("A4").Cells.Value = "Number of rows from D"
Worksheets("sheet1").Range("B4").Cells.Value = "=COUNT(tester!R[-2]C[2]:R[497]C[2])"
Worksheets("Sheet1").Range("A5").Cells.Value = "Number of rows from E"
Worksheets("sheet1").Range("B5").Cells.Value = "=COUNT(tester!R[-3]C[3]:R[496]C[3])"
Worksheets("Sheet1").Range("A6").Cells.Value = "Number of rows from F"
Worksheets("sheet1").Range("B6").Cells.Value = "=COUNT(tester!R[-4]C[4]:R[495]C[4])"
Worksheets("Sheet1").Range("A7").Cells.Value = "Number of rows from G"
Worksheets("sheet1").Range("B7").Cells.Value = "=COUNT(tester!R[-5]C[5]:R[494]C[5])"
Worksheets("Sheet1").Range("A8").Cells.Value = "Number of rows from H"
Worksheets("sheet1").Range("B8").Cells.Value = "=COUNT(tester!R[-6]C[6]:R[493]C[6])"
'Worksheets("Sheet1").Range("A9").rows.value
'Worksheets("sheet1").Range("B9").Cells.Value = "=Max(R[-7]C:R[-2]C)"

Hi,

I think you can find the last used cell in a spreadsheet with 'specialcells'.

Dim intLastRow As Integer
intLastRow = Sheets("tester").Cells.SpecialCells(xlLastCell).Row
MsgBox intLastRow

Hope this is of use.

Thanks Blue,

But I need to compare all columns as far as how many rows, then after defining that, return the entire rows data. This will ensure I have the lastest data. The spreadsheet I use will continually grow, so I need to make it dynamic with an array, I think.

Hi, though I am quite new to VBA myself, but I seem to be doing the same thing very often, hope it will help.

If you need to count the number of rows in the sheet:

_____________________________________________
Worksheets("Sheet1").Activate

j=1 'If the first non-empty cell is in the first row
Do While Not IsEmpty(Cells(j+1,1))
j=j+1
Loop
n=j
Redim MyArray(n)
________________________________________________

But this works only for uninterrupted vertical sequence of cells.

Hope it will help,
Karhu

Thanks. Now that I have counted the rows, I need to return the last rows data. 1 row X 8 Columns is the size. Should I place all elements into the array? What I mean is, should I load columnar and return last element indexed, once size is found?

Thanks Again,

Dave

Hi Dave,

If the number of columns is 8, the following will return the last row data (you know the number of rows is n)

Dim LastRowData(8) as Variant

For i=1 to 8 'If the data is in the FIRST 8 columns
LastRowData(i)=Cells(n,i)
Next i

Array LastRowData(8) will contain the values you need

Hope this will help,
Karhu