macro to transfer visible range from autofilter to array

[nevergiveup]

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.

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]

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

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]

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]

If the user will use the AutoFilter, maybe instead of RngData we can use AutoFilter.Range as below

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]

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]

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.