Assign a filtered range to array

[Jim Horg]

I have a 3row x 4col "Table1" including headers in Range("A1:D4") with row 3 and column C filtered out or hidden. Here are the VBA coes

Dim oTable as Listobject, vTable as Variant, oListRow as ListRow
Range("A2").Activate
Set oTable = ActiveCell.ListObject
vTable = oTable.DataBodyRange.SpecialCells(xlCellTypeVisible)

Array vTable above has only 1 row and 2 columns containing cells A2 and B2.
My question is:
Any simple way (in one VBA statement) to get a vTable with all the visible cells 2 rows x 3 columns with A2, B2, D2, A4, B4 and D4 without looping through the rows

For each oListRow in oTable.ListRows
    For i =....etc
        For j =....etc
            vTable(i, j) =....etc
        Next J
    Next i
Next oListRow

I am afraid the answer is No ?

 

[Smallman]

Hi

No you can’t push the visible cells into an array like this. I know it is counter intuitive but the best way to push the information in is to filter it, copy it to a spare space and then push it into the array.

Something like this which assumes Col C is hidden.

Option Explicit
 
Sub VistoAr()
    Dim vTable  As Variant
 
    Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row).AutoFilter 1, "Bris"
    Range("a2:d" & Cells(Rows.Count, 2).End(xlUp).Row).SpecialCells(12).Copy Cells(1, 10)
    [b1].AutoFilter
    vTable = Cells(1, 10).CurrentRegion
    Cells(1, 10).CurrentRegion.ClearContents
End Sub

I have attached a file to show workings.

Take care

SMallman

 

[Monique]

This excel file does nothing.

 

[NARAYANK991]

Hi Monique ,

The uploaded file does exactly what it is supposed to do.

I suggest you re-read the original post , understand what the question was , and then re-read the posted answer , and try to understand the proposed solution.

You are right that in the original question , the virtual table was not supposed to contain the hidden column , whereas the suggested solution does contain it. I am afraid that without a loop , there is no way that the hidden column can be excluded. At least that is what I think ; I may be wrong.

Since there was no response from the OP , the suggested solution may not work , but this thread is so old that it might not be profitable to revive it ; in case you have a question of your own , please start new thread.

Narayan