Fastest find method for number interval
 

Hi Reinhard,

The following code will create an array from the visible data. I have
provided several options of code that are commented out for what can be
placed in the array.

Is this what you are looking for? You can have a 2 dimensional array by
using offset to get the adjacent cells to c. Like c.Offset(0,1) will be the
adjacent cell.

Sub AutofilterArray()
Dim MyArray()
Dim lngElements As Long
Dim i As Long
Dim rngVisible As Range
Dim c As Range

'Test that Autofilter is turned on
If Sheets("Sheet1").AutoFilterMode Then
If Sheets("Sheet1").FilterMode Then
Sheets("Sheet1").ShowAllData
End If

With Sheets("Sheet1").AutoFilter.Range
.AutoFilter Field:=1, Criteria1:="=3", _
Operator:=xlAnd, Criteria2:="<4"

Set rngVisible = .Columns(1).Offset(1) _
.Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)

lngElements = rngVisible.Cells.Count

ReDim MyArray(lngElements)
i = 0
For Each c In rngVisible
'Place value of cell in array
MyArray(i) = c.Value

'Place relative address in array
'MyArray(i) = c.Address(0, 0)

'Place Absolute address in array
'MyArray(i) = c.Address(1, 1)

'Place row number in array
'MyArray(i) = c.Row

i = i + 1
Next c
End With

'For testing only.
'left here in case you want it
For i = 0 To UBound(MyArray) - 1
MsgBox MyArray(i)
Next i

Else
MsgBox "AutoFilter is not turned on" & _
vbCrLf & "Processing terminated"
End If

'Following displays the range array
'MsgBox rngVisible.Address

End Sub

--
Regards,

OssieMac

Fastest find method for number interval
 

Interesting suggestion. I'll give it a try.
Thanks
Reinhard

"OssieMac" schrieb im Newsbeitrag
...
Hi Reinhard,

The following code will create an array from the visible data. I have
provided several options of code that are commented out for what can be
placed in the array.

Is this what you are looking for? You can have a 2 dimensional array by
using offset to get the adjacent cells to c. Like c.Offset(0,1) will be
the
adjacent cell.

Sub AutofilterArray()
Dim MyArray()
Dim lngElements As Long
Dim i As Long
Dim rngVisible As Range
Dim c As Range

'Test that Autofilter is turned on
If Sheets("Sheet1").AutoFilterMode Then
If Sheets("Sheet1").FilterMode Then
Sheets("Sheet1").ShowAllData
End If

With Sheets("Sheet1").AutoFilter.Range
.AutoFilter Field:=1, Criteria1:="=3", _
Operator:=xlAnd, Criteria2:="<4"

Set rngVisible = .Columns(1).Offset(1) _
.Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)

lngElements = rngVisible.Cells.Count

ReDim MyArray(lngElements)
i = 0
For Each c In rngVisible
'Place value of cell in array
MyArray(i) = c.Value

'Place relative address in array
'MyArray(i) = c.Address(0, 0)

'Place Absolute address in array
'MyArray(i) = c.Address(1, 1)

'Place row number in array
'MyArray(i) = c.Row

i = i + 1
Next c
End With

'For testing only.
'left here in case you want it
For i = 0 To UBound(MyArray) - 1
MsgBox MyArray(i)
Next i

Else
MsgBox "AutoFilter is not turned on" & _
vbCrLf & "Processing terminated"
End If

'Following displays the range array
'MsgBox rngVisible.Address

End Sub

--
Regards,

OssieMac