Assign array

**StephenR** · 09-01-2011, 07:00 AM

Maybe this?

Sub x()

Dim r As Range, active_array

For Each r In Columns(1).SpecialCells(xlCellTypeConstants).Areas
    If r.Rows.Count > 1 Then
        active_array = r.Resize(, 2)
        ' do stuff
        Erase active_array
    End If
Next r

**welchs101** · 09-01-2011, 10:18 AM

Thanks for the response.

Here is what i came up with but for some reason i am getting a type mismatch error

active_array = ws.Range(Cell.Address(0, 0), ws.Cells(Cell.Row, Cell.End(xlDown)).End(xlDown).Offset(, 1))

any ideas why?

**welchs101** · 09-01-2011, 10:20 AM

ok...i think i fixed it ............

active_array = ws.Range(Cell.Address(0, 0), ws.Cells(Cell.Row, Cell.End(xlDown).Offset(, 1)))

still working on it but this seems to work ..........if not i will let you guys know.

actually the above did not work..........................below works ( i think)

active_array = ws.Range(Cell.Address(0, 0), ws.Cells(Cell.Row, Cell.Column).End(xlDown).Offset(, 1))

note: cell is a defined range