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.

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.

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 arrayDimar As Variant
Dim lw As Long
Dim lr As Long
Dim col As Long

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 ExplicitSub FiltertoArray() 'Excel VBA to filter to an array.

Dim rng As RangeDim rng1 As RangeDim rngArea As RangeDim ar As VariantDim sh As WorksheetDim i As LongDim j As Long