Arrays

[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

 

[Alan Beban]

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

What happened when you tried it?

Alan Beban

 

[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

 

[Lacy]

Thanks for the help.