Excel Autofilter to an Array with VBA

Recently on the Ozgrid I was involved in a discussion about adding data to an Array after it has been filtered.  If I use the current region as the Array's contents, Excel will include all of the data including the hidden data in the Array.  I suggested the user do what I always do and copy the filtered data to a new sheet and use the data on this sheet to fill the Array.  The coding is very simple and the range can be cleared after the Array is populated.

Filter Array Excel

In the above example we want to push the data from the city of London to an Array. The code to do this is as follows.

Option Explicit
Sub CopyDta() 'Excel VBA to copy data into an array
Range("A1", [A1048576]).End(xlUp)).AutoFilter 1, "London"
Range("A1").CurrentRegion.Offset(1).Copy Sheet2.[a2]
ar = Sheet2.[A2].CurrentRegion
End Sub


The above method is probably the simplest way to achieve the task. Then someone suggested the list gets sorted before the filter is applied. This insures the data is sequential. The following will do the same as the above without the need for copying the data into another sheet.

Option Explicit
Sub AddtoArr() 'Excel VBA to copy data into an array
Dim ar As Variant
Dim lw As Long
Dim lr As Long
Dim col As Long

Range("A11", [A1048576]).End(xlUp)).Sort [A11], 1 Ascending order = 1
Range("A10", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "London"
col=[a10].CurrentRegion.Columns.Count
lw=[a10].End(xlDown).Row
lr=Range("a11", Range("a" & Rows.Count).End(xlUp)).SpecialCells(12).Cells.Count
ar=Range(Cells(11, 1), Cells(lw, col)).SpecialCells(12) [a10].AutoFilter
Sheet2.[a2].Resize(lr, col)=ar

End Sub


Looping Through the Visible Cells

However, if you loop through the visible cells the task can be achieved without sorting the data. In this case the following will achieve the task. You can see it requires a lot more code but the end result is achieved without the intermediary steps in the prior 2 methods.

Option Explicit
Sub FiltertoArray() 'Excel VBA to filter to an array.
Dim rng As Range
Dim rng1 As Range
Dim rngArea As Range
Dim ar As Variant
Dim sh As Worksheet
Dim i As Long
Dim j As Long

Range("A10", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "London"
Set sh=Sheet1
Set rng=sh.AutoFilter.Range.Offset(1, 0).Resize(sh.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(12)
ReDim ar(1 To sh.AutoFilter.Range.Rows.Count - 1, 1 To 4)

For Each rngArea In rng.Areas
For Each rng1 In rngArea
i=i + 1
For j=0 To 3
ar(i, 1 + j)=rng1.Offset(0, j)
Next j
Next rng1
Next rngArea
Sheet2.Range("A1").Resize(UBound(ar), 4)=ar
End Sub


The attached Excel file shows all three VBA methods.