AutoFiltering problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

This is the autofilter I am using to obtain filtered dat

dim myArray()
dim rngSrc as Rang
ActiveWorkSheet.UsedRange.AutoFilter Field:=1, Criteria1:="Jim", visibledropdown:=Fals
Set rngSrc = ActiveWorkSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible
myArray=rngSrc.Cell

If I loop through rngSrc on each row, I obtain all the records
but If I put this in rngSrc.Cells in a variant array, I am obtaining only 6 records

Am I doing anything wrong here

Please help me to get over this proble

Thanks

Amar
 
You can't pick up a non-contiguous range of cells in an array in one
operation. You would need to loop through each visible row and populate
your array.

dim myArray()
dim rngSrc as Range, cell as range
dim i as long, j as long, rng1 as Range
ActiveWorkSheet.UsedRange.AutoFilter Field:=1, Criteria1:="Jim",
visibledropdown:=False
Set rngSrc =
ActiveWorkSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
set rng1 = Intersect(columns(rngSrc.Column),rng.Src)
Redim myarray(1 to rng1.count, 1 to rngSrc.Columns.count)
i = 0
for each cell in rng1
i = i + 1
for j = 0 to rngSrc.Columns.Count - 1
myarray(i,j+1) = cell.offset(0,j).Value
next
Next

Untested.
 
Back
Top