Getting Array Data from a Filtered List in VBA
Getting data from an Excel sheet into an array is usually best accomplished with a statement that looks something like this:
I thought it would be keen to fill an array from a filtered list, so I coded
Dim vArr As Variant
vArr = Sheet1.UsedRange.SpecialCells(xlCellTypeVisible).Value
Stop
End Sub
on this list
The SpecialCells returns a range of only those cells that are visible, i.e. unfiltered in this case. The problem, it turns out, is that this method doesn’t work with noncontiguous ranges and that’s just what SpecialCells returns. I put the Stop in there so I could check the Locals Window.
It filled from the first Area of the range, then stopped. I confirmed that it was the lack of continutity of the range with this code
Dim vArr As Variant
vArr = Union(Sheet1.Range("A1:C1"), Sheet1.Range("A4:C6")).Value
Stop
End Sub
which returned similar results. So I’m stuck iterating through the range, I guess. But then my array is backward; column, row instead row, column because I can’t change the first element of an array with Redim Preserve.
Dim rRow As Range
Dim aArr() As String
Dim i As Long
Dim lCount As Long
ReDim aArr(1 To 3, 1 To Sheet1.UsedRange.Rows.Count)
lCount = 0
For Each rRow In Sheet1.UsedRange.Rows
If rRow.Hidden = False Then
lCount = lCount + 1
For i = 1 To 3
aArr(i, lCount) = rRow.Cells(i).Value
Next i
End If
Next rRow
ReDim Preserve aArr(1 To 3, 1 To lCount)
Stop
End Sub
Is there a better way?
‘Uses a variant array
‘Uses the range that is auto filtered
‘Transposes the filled array
‘Me thinks you may have something else in mind?
Sub ArrFilteredList2()
Dim rRow As Range
Dim vArr As Variant
Dim i As Long
Dim lCount As Long
Dim af As AutoFilter
Dim rng As Range
Set af = ActiveSheet.AutoFilter
Set rng = af.Range
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
ReDim vArr(1 To 3, 1 To rng.Rows.Count)
For Each rRow In rng.Rows
If rRow.Hidden = False Then
lCount = lCount + 1
For i = 1 To 3
vArr(i, lCount) = rRow.Cells(i).Value
Next i
End If
Next rRow
ReDim Preserve vArr(1 To 3, 1 To lCount)
vArr = Application.Transpose(vArr)
‘Stop
MsgBox UBound(vArr, 1) & vbCr & UBound(vArr, 2)
End Sub
Are you committed to using an autofilter? If not, an advanced filter extracting to a new range would do the trick. Or, a SQL query perhaps?
The simplest way:
copy the cells to an empty space
read the data there
.autofilter 1,"test"
.offset(1).copy sheets(1).cells(1,27)
.autofilter
End with
sq=sheets(1).cells(1,27).currentregion
sheets(1).cells(1,27).currentregion.clearcontents
I forgot
.autofilter 1,"test"
.offset(1).specialcells(xlcelltypevisible).copy sheets(1).cells(1,27)
.autofilter
End with
sq=sheets(1).cells(1,27).currentregion
sheets(1).cells(1,27).currentregion.clearcontents
I might be tempted to read the full (unfiltered) range into an array (to keep the number of sheet read actions down) and then put each row into a collection element, filtering out unwanted elements during the process.
I tend to do my manipulations on the collection elements, then write to an array to load onto the sheet, but you could load the array from the collection before manipulation.
I DO like geoff’s SQL idea, though.
This worked for me…
””’
Dim rRow As Range
Dim aArr() As Variant
Dim i As Long
Dim lCount As Long
Dim CellCount As Variant
Dim Range_To_Get As Variant
CellCount = Sheets(1).UsedRange.Rows.Count
Range_To_Get = Sheets(1).UsedRange.Address
ReDim aArr(1 To 3, 1 To CellCount)
lCount = 1
i = 1
For Each rRow In Sheets(1).Range(Range_To_Get)
If lCount = 4 Then
i = i + 1
lCount = 1
End If
If rRow.Rows.Hidden = False Then
aArr(lCount, i) = rRow
Else
GoTo Devo:
End If
lCount = lCount + 1
Devo:
Next
ReDim Preserve aArr(1 To 3, 1 To i)
aArr = Application.Transpose(aArr)
End Sub
Good Luck
Devo
If AutoFiltering is enabled, and an AutoFilter is in effect, AutoFilter.Range appears to be a pseudo UsedRange which includes all cells in scope for auto-filtering. The range it refers to doesn`t change with different filtered row counts.
I used Intersect, feeding in xlVisible and AutoFilter.Range to get what I needed to create a chart from the filtered rows.
(You have to adjust AutoFilter.Range to remove the first row).
Not thoroughly tested as I`m referring back to an old bit of code.
Any good?