Arrays

L

Lacy

I'm using an array, but I don't know the number of rows.
Does anyone know if I can do this.

Dim Defect() As Variant, Sum As Single, RowCount As Variant
Dim Count As Single, GrandTotal As Integer


RowCount = ActiveWorkbook.Worksheets("Sheet1").Cells
(Rows.Count, 10).End(xlUp).Value <> ""
ReDim Defect(RowCount, 34)

For i = 1 To RowCount
For j = 1 To 34
Defect(i, j) = ActiveWorkbook.Worksheets
("Sheet1").Cells(i, j)
Next j
Next i

Thanks,
Lacy
 
A

Alan Beban

Lacy said:
I'm using an array, but I don't know the number of rows.
Does anyone know if I can do this.

Dim Defect() As Variant, Sum As Single, RowCount As Variant
Dim Count As Single, GrandTotal As Integer


RowCount = ActiveWorkbook.Worksheets("Sheet1").Cells
(Rows.Count, 10).End(xlUp).Value <> ""
ReDim Defect(RowCount, 34)

For i = 1 To RowCount
For j = 1 To 34
Defect(i, j) = ActiveWorkbook.Worksheets
("Sheet1").Cells(i, j)
Next j
Next i

Thanks,
Lacy
Click to expand...
What happened when you tried it?

Alan Beban
 
M

Mike Waldron

Lacey,

I think this is easier.

rowcount = Activesheet.UsedRange.Rows.Count
or
rowcount = Activecell.CurrentRegion.Rows.Count
if you already have a cell selected

CurrentRegion and UsedRange are explained in the Help

Regards,
Mike
 
Top