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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top