AutoFilter.Range Visible Cells to Array?

[Dennis]

The below snippet worked for me when I wanted to transfer an AutoFilter.Range of
visible cells only to an Array.

The code is a bit crude but this is the only way I could get it to work after
many different trials. Is there a more efficient way?

Thanks

Dennis

======================================================================
dim myArr as variant
dim rng1 as range, FRng as range
dim LastCol as long, FRows as long

LastCol = .Sheets(1).Range("A1").End(xlToRight).Column
<snip AutoFilter code>
With Sheets(1).AutoFilter.Range
Set rng1 = .Columns(1).SpecialCells(xlCellTypeVisible)
FRows = rng1.Count
Set FRng = .SpecialCells(xlCellTypeVisible).Range("a1"). _
Resize(FRows, LastCol)
End With
myArr = FRng

 

[Dennis]

Whoops sorry the below code *did not* save the autofilter visible into myArr.
It just saved the sheets(1).range("A1").Resize(FRows, LastCol) pre-autofilter
values into myArr.

Back to the drawing board.

Dennis

 

[Dennis]

This code correctly gets the filtered range and the number of rows in the
filtered range

Set rng = Sheets(1).AutoFilter.Range
Set FRng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
With Sheets(1).AutoFilter.Range
Set rng1 = .Columns(1).SpecialCells(xlCellTypeVisible)
FRows = rng1.Count
end with

However when I try to put this range into an array

myArr = FRng

The UBound(myArr) is 1 and not FRows

I tried myArr = FRng.Resize(FRows, LastCol) but this did not work either.

Any suggestions on how to get the autofilter visible range into an array?

Thanks

Dennis

 

[Dave Peterson]

methinks you'll have to cycle through the visible cells.

Option Explicit
Sub testme()

Dim myArr As Variant
Dim myCell As Range
Dim FRng As Range
Dim rCtr As Long
Dim cCtr As Long

With Sheets(1).AutoFilter.Range
Set FRng = Nothing
On Error Resume Next
'resize it to one column and avoid the header row
Set FRng = .Offset(1).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If FRng Is Nothing Then
MsgBox "no visible rows shown"
Exit Sub
End If

ReDim myArr(1 To .Rows.Count - 1, 1 To .Columns.Count)

rCtr = 0
For Each myCell In FRng.Cells
rCtr = rCtr + 1
For cCtr = 1 To .Columns.Count
myArr(rCtr, cCtr) = myCell.Offset(0, cCtr - 1).Value
Next cCtr
Next myCell
End With
End Sub

 

[Dennis]

Thanks Dave.

After working on every possible combination for days, I came to the same
conclusion.

It seems that if the rows or cells are not one after the other then the
myArr=FRng just takes the first rows that are right next to each other. In my
case that was row 1. When you do an autofilter I guess that Excel just flags
the rows that satisfy the autofilter. If the flagged rows are not next to each
other then the visible to array assignment will not work.

Unfortunately this defeats my speedup purpose of putting the filtered range into
an array, operating on the elements of the array and moving the final
computation back into a range. I'm using an Excel Object in VB and operating on
the object cell level brings things to a crawl, while operating on an array is
super fast. Even in Excel operating on individual cells slows things down.

My idea which I haven't done yet was to loop through the autofilter range
deleting each row that was not visible(because my filters work on columns) and
moving the resultant to an array or moving each visible row to an array.

If that doesn't work then it looks like I'll have to write the filter routines
in VB6 and operate on the original prefiltered Array.

Thanks again for the coding lesson.

Dennis

 

[Dennis]

Actually I thought of 2 faster methods to copy the visible cells to an array.

(1) Copy the visible range FRng to a New worksheet. On the new worksheet there
will only be the rows of the visible range. Then just do the regular
myArr=NewSheetRange.

(2) Copy the visible range FRng to clipboard then paste clipboard into array. I
don't know for sure if the paste part will work.

Dennis

 

[Dennis]

(e-mail address removed) wrote:

I tried method (1) below and it works! Below is the code.

I'll have to see if the copy to clipboard->paste to Array works also.

Dim XLWB as Workbook, XLWS as WorkSheet
Dim rng2 as Range, FRng as Range
dim OSRows as Long, LastRow2 as Long, LastCol as Long
Dim TT as String
TT="TMP"
Set XLWB = ActiveWorkBook
Set XLWS = XLWB.Sheets(1)
LastRow = XLWS.Cells(Rows.Count, "a").End(xlUp).Row
LastCol = XLWS.Range("A1").End(xlToRight).Column
With XLWB
Worksheets.Add(After:=.Sheets(Sheets.Count)).Name = TT
Set rng2 = .Sheets(TT).Range("a1")
rng2.Rows.Clear 'if this routine is in loop must clear
Set rng = XLWS.AutoFilter.Range
Set FRng = rng.SpecialCells(xlCellTypeVisible)
FRng.Copy rng2
OSrows = rng2.Columns(1).Count
LastRow2 = .Sheets(TT).Cells(Rows.Count, "a").End(xlUp).Row
OSArr = rng2.Resize(LastRow2, LastCol)
End With 'XLWB
MsgBox (" OSArrSize=" & UBound(OSArr) & "x" & UBound(OSArr, 2))

Actually I thought of 2 faster methods to copy the visible cells to an array.

(1) Copy the visible range FRng to a New worksheet. On the new worksheet there
will only be the rows of the visible range. Then just do the regular
myArr=NewSheetRange.

(2) Copy the visible range FRng to clipboard then paste clipboard into array. I
don't know for sure if the paste part will work.

Dennis

<snip>