A weird .SpecialCells(XlCellTypeVisible) behavior

[dourpil]

Hi everyone,

I found something quite weird:

On a filtered table (where a lot of rows are hidden due to the filters), I refer to a cell using tablerange.SpecialCells(XlCellTypeVisible).Cells(3,1) (where I set my tablerange as .ListObjects("TableName").DataBodyRange)

I thought it would logically refer to the third visible row on my filtered table.

As it turns out, when referring to XlCellTypeVisible, it seems that only the first hidden cells are correctly taken out.

If my table is filtered in such a way that only the following row numbers are visible (3,4,6 which means my rows 2 and 5 are hidden), referring to tablerange.SpecialCells(XlCellTypeVisible).Cells(1,1) correctly returns the cell in row 3 (the first visible cell); whereas referring to tablerange.SpecialCells(XlCellTypeVisible).Cells(3,1) returns the cell in row 5 (a hidden row!).
It seems to me that XlCellTypeVisible only takes into account the first visible cell it sees and then starts from there.

Anyone could enlighten me on the workings of that method?

Thanks in advance!

 

[NARAYANK991]

Hi ,

This is normal ; once you filter a range , the rows which are visible may or may not be contiguous. Excel will therefore refer to them as Areas , with each filtered section forming an area.

For instance , I have a range of data which has been filtered ; suppose I run a macro for this as follows :

Public Sub temp()
          Dim cell As Range
          Set r = ActiveSheet.Range("C6:C8700").SpecialCells(xlCellTypeVisible)
          For Each cell In r.Cells
              MsgBox cell.Address
          Next
End Sub

When I type in the Immediate window :

?r.address

Excel displays the following :

$C$6,$C$294,$C$582,$C$870,$C$1158,$C$1446,$C$1734,$C$2022,$C$2310,$C$2598,$C$2886,$C$3174,$C$3462,$C$3750,$C$4038,$C$4326,$C$4614,$C$4902,$C$5190,$C$5478,$C$5766,$C$6054,$C$6342,$C$6630,$C$6918,$C$7206,$C$7494,$C$7782,$C$8070,$C$8358,$C$8646

Running the above code will display each of the above addresses , one by one.

Narayan

 

[dourpil]

Hi Narayan and thank you for your kind reply.

I'm still having trouble understanding why Visible.cells(1,1) refers to the first visible cell, correctly ignoring initial hidden rows, but Visible.cells(2,1) refers to the row right after the first visible one, regardless of whether that one is visible or not.

How would you set up references in a filtered table? The context I'm working with is a userform where listboxes filter and display the visible cells of my table. A user can select a row in the listbox and, through textboxes, can change the values on that row. I thought I could get the .ListIndex of my listbox (the row number of the selected line) and use it to directly change my table (using Visible.Cells(.Listindex, ...)).

I found a way around it by setting up a loop in my table where if the cell is visible, X becomes X+1 until it reaches .ListIndex. And then when X = ListIndex, the row number I'm at is the one where my value needs to change.
Not sure how good that solution is but it seems to do the trick for now.