SpecialCells Select problem

[G Lykos]

Help! Have the following snippet, am running into a problem were the
autofilter screens out two records in the data set but the selection set
number of items and the selection set count are different!

Selection.Autofilter Field:=110, Criteria1:="<>Test"
Range("A1").CurrentRegion.Sort , Header:=xlYes, _
Key1:=Range("DG1"), Key2:=Range("AZ1")
Range("DG1").Offset(1).Select
Range(Selection,
Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Select
Set ASN = Selection
iMax = ASN.Count

The final selection count, and iMax, reflect the correct number of visible
cells. However, range ASN contains all cells, both visible and invisible,
determined by stepping through debug.printing ASN(n). Why am I picking up
invisible cells in the selection set, and how can the selection set count be
different than the number of selection set elements??

A subsequent work-around was to copy the entire worksheet after filtering
and sorting and paste it into a scratch worksheet, then set ASN there, but
I'd much prefer to avoid that gyration. Any ideas?

Windows XP, Excel 2003 SP3.

Thanks!
George

 

[OssieMac]

Hi George,

I did some testing on this. The following did not work because it starts at
the first visible cell and increments by 1 and includes all addresses after
the first visible cell. Not what I would have expected:-

iMax = ASN.Count
For i = 1 To iMax
MsgBox ASN(i).Address
Next i

However, this method did work. It only displays the addresses of the visible
cells. Hopefully using a 'For Each loop' will solve your problem:-

For Each c In ASN
MsgBox c.Address
Next c

Regards,

OssieMac

 

[G Lykos]

OssieMac, thanks for your suggestion. I'll take a look at your alternative
structure, but know that there is a fair amount of subsequent logic that
might then need significant restructuring.

Out of curiosity, if you still have your test data - does the selection set
(using SpecialCells) element count coincide correctly with the number of
selection set elements? In my case, the several invisible elements were
embedded in the middle of the original data set, and the selection using
SpecialCells pulled in all elements, even though the count reflected only
the visible elements in the original data set. As the first element of my
original data set is visible, what I encountered is potentially consistent
with what you observed with yours.

I guess what you may be suggesting is that the collection index access
method, ASN(i), is flawed, while the collection count method, ASN.count, is
not. If this is a bug, then I'll be curious to see if it also existed in
Excel 2003 SP2, SP3 having just been released and what I'm on.

George

 

[G Lykos]

Although on second thought, if the SpecialCells Select were working
properly, then you wouldn't expect that the collection would have the
invisible cells in it in the first place - unless the selection doesn't
truly form a separate collection of data, but rather creates pointers into a
"visible" subset (well, if it worked correctly) of the original data.

 

[Peter T]

Hi George,

SpecialCells has a limit of 8192 areas, could that be the problem. If so
work with chunks of up to 16000 rows at a time in the entire selection.

Regards,
Peter T

 

[G Lykos]

OssieMac, thought some more about what you said, realized that this is
"deja-vu all over again", and means that there are still basic flaws in the
count and index methods when used with selections involving invisible cells.

Bugs 1 (which you articulated) and 2:
Create a vertical list of header and three data cells. Set an autofilter
that hides the first and third cells. Set a range R using
SpecialCells(xlCellTypeVisible).Select to the three cells: Debug.Print R(1)
yields the second (visible) cell, Debug.Print R(2) yields the third
(invisible) cell [ouch], Debug.Print R(3) yields a fourth cell, outside of
the selection [ouch].

Bug 3 (not mentioned before):
Create a vertical list of header and one data cell. Set a range R using
SpecialCells(xlCellTypeVisible).Select to the cell; Debug.Print R.count =
1. Now set an autofilter that hides the cell, set range R using
SpecialCells(xlCellTypeVisible).Select to the cell; Debug.Print R.count =
16776960 [ouch].

I just checked this in Excel 97. It's really hard to believe that these
same basic bugs are alive and well in Excel 2003 SP3. Wonder if Excel 2007
is the same.

Regards,
George

 

[G Lykos]

Peter, thanks for the information and suggestion. In this case, there are
670 cells in the list, of which two are filtered out. Please see my
separate post just now to OssieMac describing three apparent BUGS related to
invisible cells in a selection - if you should have further information, I'm
certainly interested.

Regards,
George

 

[Peter T]

In a light test I cannot recreate your discrepancy.
Maybe this is misleading you -
If ASN is a multi-area range, which no doubt it is, you need to do

for each rArea in ASN.Areas
for each cel in rArea
debug.? cel.address, cel.value
or
for n = 1 to rArea.count
debug.? rArea(n).whatever
next: next

Regards,
Peter T

 

[OssieMac]

I have just got back to my computer and done some more testing and the only
way I can get it to work is the method that I posted before using For Each
loop. I have tested in both xl2002 and xl2007.

Just as an added extra, I have previously had problems in the interactive
mode actually selecting the visible cells in a filtered list for copy and
paste. All cells get selected and pasted. To get around it I have had to Go
to Special and select visible Cells only. This is an intermittent problem
that Ron de Bruin MVP said "more people have experienced this problem".

Regards,

OssieMac

 

[Peter T]

Whilst the single For-Each loop works personally I prefer to loop areas when
dealing with a potential multi area range, either with For-To or For-Each,
eg

Sub test()
Dim i&, j&, n&
Dim rng As Range, rArea As Range, cel As Range

Set rng = Range("A1:A3,A7:A10")
' the 5th cell in this range is A8, not A5

Debug.Print rng(5).Address ' A5 wrong
' this represents the 5th cell of the first area, which extends
' beyond the area

n = 0
For Each cel In rng
n = n + 1
If n = 5 Then Debug.Print cel.Address
Next

' or
n = 0
For Each rArea In rng
For Each cel In rArea
n = n + 1
If n = 5 Then Debug.Print cel.Address
Next
Next

'or
n = 0
For i = 1 To rng.Areas.Count
For j = 1 To rng.Areas(i).Count
n = n + 1
If n = 5 Then Debug.Print rng.Areas(i)(j).Address
Next
Next

End Sub

I can't find any reference to the "intermittent problem" you say Ron de
Bruin has referred to..

Regards,
Peter T

 

[Dave Peterson]

Dim HowManyVisRows as long
dim VisRng as range

With worksheets("somesheetname").autofilter.range
'subtract one for the header.
howmanyvisrows _
= .columns(1).cells.specialcells(xlcelltypevisible).cells.count - 1

if howmanyvisrows > 0 then
'avoid the header and come down one row
set visrng = .resize(.rows.count-1).offset(1,0) _
.cells.specialcells(xlcelltypevisible)
else
set visrng = nothing
end if
end with

if visrng is nothing then
'warning message???
else
'do what you want
end if

Untested, uncompiled--watch for typos.

 

[OssieMac]

Hi Dave,

If you insert the following at 'do what you want' then I believe it
demonstrates what George has been referring to. It appears that the cells
cannot be referenced using VisRng(i). However, as per my previous post, For
Each c In VisRng works.

'Following does not work.
'VisRng.Count is correct.
'Starts at correct cell but loops through
'all cells (including non visible) from
'that point until i > VisRng.Count.

For i = 1 To VisRng.Count
MsgBox "Address = " & VisRng(i).Address & Chr(13) _
& "Value = " & VisRng(i).Value
Next i

'This does work and only displays address
'and values of visible cells.
For Each c In VisRng
MsgBox "Address = " & c.Address & Chr(13) _
& "Value = " & c.Value
Next c

Regards,

OssieMac

 

[Dave Peterson]

You're right. VisRng(i) won't exclude hidden cells.