CODEWITHSUNDEEP
excelrangevisiblelistobjectvba
sifar
sifar

Reputation: 1148

selecting SpecialCells(xlCellTypeVisible) from specific columns of ListObject table

I am trying to get only the visible cells of certain columns of a ListObject table into a range object.

This doesn't seem to work.

dim rng as range
with activesheet.listobjects("Tab_data").databodyrange
    set rng=.specialcells(xlcelltypevisible)
end with

but this works when i select entire range then offset 1st column to select the other 2 required columns!

dim rng as range
with activesheet.usedrange
    Set rng = .Offset(1, 1).Resize(.Rows.Count-1, .Columns.Count-1).SpecialCells(xlCellTypeVisible)
end with

but i cannot use the above in a formula as my formula is referring to only the 2 columns in the listobject shown below:

enter image description here

UDF Formula on worksheet:

=TagCloud(RngWrdLst as Range)

and i am using it as:

=TagCloud(tab_data[[Brands]:[Index]])

As you can see from the image, i only want the visible cell ranges from columns "Brands" & "Index" and not the cells from "COLUMN" column.

so the visible ranges i would like to have are:

"$B$2:$C$3,$B$45:$C$45,$B$75:$C$78"

edit for @Jeeped :

If i have a UDF function being called from a worksheet cell and passed a ListObject range of columns B & C (only these columns and not entire databodyrange), then how am i going to find the RngWrdLst visible range?

e.g.

call from worksheet:

=TagCloud(tab_data[[Brands]:[Index]])

Function definition:

Function TagCloud(RngWrdLst As Range)
Dim VisibleRng As Range

With RngWrdLst
    Set VisibleRng = Intersect(.SpecialCells(xlCellTypeVisible), Union(.Columns(2), .Columns(3)))
    Debug.Print VisibleRng.Address(0, 0)
End With

'   do something with the visibleRng......
End Function

BTW, RngWrdLst would contain the 2 columns B & C. So how do i modify your code and get only the visible range from the function?

Upvotes: 1

Views: 4427

Answers (2)

Radhish Radhakrishnan
Radhish Radhakrishnan

Reputation: 1

I found that error with Databody range it breaksdown filtered rows in diffrent set of range areas. So we can tackle this range area by always having all continuous rows of data when its filtered.

This can be achieved by sorting the data your data. Once you sort the data and then filter. all th filtered data in continous rows of data & databodyrange cannot miss.

'set tbl as list object

set tbl = Sheets("Main_DB").ListObjects("Main") 'change listobject name

result = Application.WorksheetFunction.Unique([Main[x]]) ' Main[x] change "x" with name of column you want to filter.

For i = LBound(result) To UBound(result)

    prim_key = result(i, 1)
    With tbl.Sort
        .SortFields.clear
        .SortFields.Add Key:=tbl.DataBodyRange.Columns(x), SortOn:=xlSortOnValues  ' change Columns(x) number with your choice
        .Header = xlYes
        .Apply
    End With
    tbl.Range.AutoFilter Field:=3, Criteria1:=prim_key
    rowz = tbl.DataBodyRange.Columns(2).SpecialCells(xlCellTypeVisible).Count
    mArray = Sheets("Main_DB").ListObjects("Main").DataBodyRange.SpecialCells(xlCellTypeVisible)
enter code here to use mArray

Upvotes: 0

user4039065
user4039065

Reputation:

Use the Intersect method on a Union method of the columns you want.

Dim rng As Range
With ActiveSheet.ListObjects("Tab_data").DataBodyRange
    Set rng = Intersect(.SpecialCells(xlCellTypeVisible), _
                        Union(.Columns(2), .Columns(3)))
    Debug.Print rng.Address(0, 0)
End With

Alternately, shift right off the first column and resize one column less than the .DataBodyRange property contains.

Dim rng As Range
With ActiveSheet.ListObjects("Tab_data").DataBodyRange
    With .Resize(.Rows.Count, .Columns.Count - 1).Offset(0, 1)
        Set rng = .SpecialCells(xlCellTypeVisible)
    End With
    Debug.Print rng.Address(0, 0)
End With

Depending upon what you wnt to do with rng, you may have to loop through the Range.Areas property.

Upvotes: 1

Related Questions