Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find number of particular values over an interval | Excel Discussion (Misc queries) | |||
Fastest find method for number interval | Excel Programming | |||
How to find maximum non-blank row number without brute force method? | Excel Programming | |||
Bulk Row Deletion - Fastest method | Excel Programming | |||
Find fastest way to do lookups | Excel Programming |