macro to transfer visible range from autofilter to array

nevergiveup

New Member
Joined
Jun 2, 2011
Messages
43
I have the code below to transfer the visible cells from a range in excel into an array. In this case, the non-filtered range is a11:a19. Applying the auto-filter, the visible rows become rows 11,12 and 14. However, running the macro below the textbox results include only the text in cells a11 and a12. Since cell a14 is visible (and thus part of the range), why is it not included in the array? What should be changed so that cell a14 also is in myarray?

Supplemental question: if I declared Option Explicit, what DIM statement should be used with x. See For X = ..... in the code below.

Code:
Sub ArrayFromRange()

Dim firstviscell As Range
Dim visrange As Range
Dim MyArray As Variant

Set firstviscell = Range("firstinlist") ' firstinlist is named cell which happens to be cell "a11"

MyArray = Range(firstviscell, firstviscell.End(xlDown)).SpecialCells(xlCellTypeVisible).Value

For x = LBound(MyArray) To UBound(MyArray)
    MsgBox MyArray(x, 1)

Next
End Sub
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,344
Hi,

The visible range is divided in Areas. Your code is getting only the values in the first area (A11:A12)

Maybe something like this
Assumes
Sheetname: Sheet1
Header in A10
Data in A11:A19
FilterCriteria: "A*"

Adjust to your real case

Code:
Sub filterToArray()
    Dim rngData As Range, rngVisible As Range
    Dim rCell As Range, myArray() As Variant, i As Long
 
    With Sheets("Sheet1")  '<---Adjust
        'Remove the Auto Filter
        .AutoFilterMode = False
 
        'Set the data range  <-- Adjust
        Set rngData = .Range("A10:A19")
 
        'Apply the Auto filter to data range, criteria = "A*"  <-- Adjust
        rngData.AutoFilter Field:=1, Criteria1:="A*"
 
        'Set the visible range
        With rngData
            Set rngVisible = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                  .SpecialCells(xlCellTypeVisible)
        End With        
 
        'Loop through visible range and populate the array
        For Each rCell In rngVisible
            i = i + 1
            ReDim Preserve myArray(1 To i)
            myArray(i) = rCell
            MsgBox myArray(i)
        Next rCell
    End With
 
End Sub

M.
 

nevergiveup

New Member
Joined
Jun 2, 2011
Messages
43
Thanks, Marcelo. This makes sense. However, I would like the user to be able to use the autofilter tool directly from the spreadsheet (as opposed to having the autofilter in the code). Perhaps I could capture rngdata prior to running the macro then have the macro reference rngdata. Seems like that would avoid having to clear the autofilter then run it again.

Any thoughts on how I might do that? A user defined function perhaps? Or perhaps I could clear the autofilter then refilter the exact same way it was filtered? Thanks again.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,344
If the user will use the AutoFilter, maybe instead of RngData we can use AutoFilter.Range as below

Code:
Sub filterToArray2()
    Dim rngVisible As Range
    Dim rCell As Range, myArray() As Variant, i As Long
 
    With Sheets("Sheet1")  '<---Adjust
         If .AutoFilterMode = False Then MsgBox "No filter set up", vbCritical: Exit Sub
         
        'Set the visible range
        With .AutoFilter.Range
            Set rngVisible = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                  .SpecialCells(xlCellTypeVisible)
        End With
        
        'Loop through visible range and populate the array
        For Each rCell In rngVisible
            i = i + 1
            ReDim Preserve myArray(1 To i)
            myArray(i) = rCell
            MsgBox myArray(i)
        Next rCell
        
    End With
    
End Sub
Is it ok?

M.
 

nevergiveup

New Member
Joined
Jun 2, 2011
Messages
43
M, first, let me say that this works perfectly as long as their is an autofilter (even if nothing is being filtered out).

I now need to set it up where it will work with an autofilter our without the autofilter. I can probably figure it out but if you feel like adding the necessary if / then, I would greatly appreciate your input if you are willing Either way, you solved the hard part. Many many thanks!
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,344
You are welcome! Glad for helping :)

About your new problem
I now need to set it up where it will work with an autofilter our without the autofilter
In the last macro i inserted this code line

If .AutoFilterMode = False Then MsgBox "No filter set up", vbCritical: Exit Sub

just to deal with this situation. Or am i missing something?

M.
 
Last edited:

Forum statistics

Threads
1,085,840
Messages
5,386,272
Members
401,990
Latest member
Lochlan 1997

Some videos you may like

This Week's Hot Topics

Top